-- 统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt,以及该年的总体情况, -- 月总刷题数 日均刷题数=月总刷题数/月天数 年总刷题数=月总刷题数之和 -- 1、先过滤,获取月份字符列,月天数;2、再对月份分组,统计,计算日均刷题数 3、再汇总,除以31,union拼接 SELECT st, COUNT(1) month_q_th, ROUND(COUNT(1) / days, 3) avg_day_q_th FROM (SELECT *, DATE_FORMAT(submit_time, '%Y%m') st, DAYOFMONTH(LAST_DAY(submit_time)) days FROM practice_record WHERE YEAR(submit_time) = '2021') a GROUP BY st , days UNION SELECT '2021汇总', SUM(month_q_th), ROUND(SUM(month_q_th) / 31, 3) FROM (SELECT st, COUNT(1) month_q_th FROM (SELECT *, DATE_FORMAT(submit_time, '%Y%m') st, DAYOFMONTH(LAST_DAY(submit_time)) days FROM practice_record WHERE YEAR(submit_time) = '2021') a GROUP BY st , days) b order by st;