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

SELECT submit_month, ROUND(COUNT(1),3) month_q_cnt, 
    ROUND(COUNT(1)/days,3)  avg_day_q_cnt
FROM  (
    SELECT *, DATE_FORMAT(submit_time, '%Y%m') submit_month, DAY(LAST_DAY(submit_time)) days
    FROM practice_record
) t1
WHERE YEAR(submit_time) = 2021
GROUP BY submit_month, days
union 
SELECT '2021汇总' submit_month, COUNT(1) month_q_cnt, ROUND(COUNT(question_id)/31,3) avg_day_q_cnt
FROM  practice_record
WHERE YEAR(submit_time) = 2021
ORDER BY submit_month

最近在做的朋友可以参考下我这个，能正常运行且不发生this is incompatible with sql_mode=only_full_group_by的错误，这里主要是最新版要求SELECT 出现的字段（如这里的submit_month，days）必须在GROUP BY 出现，除了聚合函数里面的字段。所以可以先在FROM 建立一个子查询，列出submit_month和天数，再进行后续查询