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

SELECT COALESCE(t1.submit_month1,'2021汇总') submit_month, ROUND(COUNT(1),3) month_q_cnt, 
    ROUND(COUNT(1)/MAX(days),3)  avg_day_q_cnt
FROM  (
    SELECT *, DATE_FORMAT(submit_time, '%Y%m') submit_month1, DAY(LAST_DAY(submit_time)) days
    FROM practice_record
) t1
WHERE YEAR(submit_time) = 2021
GROUP BY t1.submit_month1
WITH ROLLUP 

学习到了一个新知识coalesce()，用来返回第一个非null的值，由于SELECT是一行行的取值，所以在取完最后一行之后可以执行coalesce()第二个参数的值，因此可以与WITH ROLLUP搭配