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

再次回顾这道题提交了好多次代码都出现同样的关于“group by”的报错记录，反复看了好久才发现问题出现在DAY(LAST_DAY(submit_time))，即每个月都有好多个submit_time,随之对应的就是last_day返回好多个31或者30，count(question_id)是一个数，假定为6，那么6/（30，30，30，30，30，30）必然报错，所以我们要去重，用max,min,avg都行。

但是奇怪的是，我之前做这道题的时候没有去重也能提交成功......

SELECT DATE_FORMAT(submit_time, '%Y%m') AS submit_month,
       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 submit_time IS NOT NULL AND 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 submit_time IS NOT NULL AND YEAR(submit_time)='2021'
ORDER BY submit_month