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

(SELECT DATE_FORMAT(submit_time,'%Y%m') AS submit_month,
COUNT(*) AS month_q_cnt, 
ROUND(COUNT(*)/DAY(LAST_DAY(submit_time)),3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
GROUP BY submit_month

UNION

SELECT '2021汇总' AS submit_month,
COUNT(*) AS month_q_cnt,
ROUND(COUNT(*)/31,3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021)
ORDER BY submit_month

这里要mark的是： （1）获取对应月份的天数：LAST_DAY(date) 输入一个日期或日期时间值，返回该月的最后一天对应的日期。 （2）汇总行的构建：通过UNION添加汇总行，汇总行的名字利用【SELECT '2021汇总' AS submit_month】解决。