题解 | 月总刷题数和日均刷题数

SELECT

    DATE_FORMAT (submit_time, '%Y%m') AS submit_month, -- 提取年月（如202108）

    COUNT(question_id) AS month_q_cnt, -- 每月刷题数

    ROUND(

        COUNT(question_id) / max(DAY (LAST_DAY (submit_time))),

        3

    ) AS avg_day_q_cnt -- 每月的日均刷题数

FROM

    practice_record

where year(submit_time) = 2021

GROUP BY

    DATE_FORMAT (submit_time, '%Y%m')

UNION ALL

SELECT

    '2021汇总' AS submit_month, -- 汇总行

    COUNT(question_id) AS month_q_cnt, -- 总刷题数

    ROUND(COUNT(question_id) / 31, 3) AS avg_day_q_cnt -- 全年日均刷题数

FROM

    practice_record

where year(submit_time) = 2021

order by

    submit_month  

;

-- DAY (LAST_DAY (submit_time),在 SQL 模式 ONLY_FULL_GROUP_BY 下，这会导致语法错误。具体的错误信息是，submit_time 在 SELECT 语句中使用了，但在 GROUP BY 中没有涉及。使用MAX完美解决

-- 在 SQL 中，ORDER BY 一般应该放在整个查询的最后部分，而不能在 UNION ALL 的某个子查询部分单独使用。