题解 | 月总刷题数和日均刷题数
月总刷题数和日均刷题数
https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
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 的某个子查询部分单独使用。
投递东方财富等公司9个岗位 >
