MySQL coalesce 和 with rollup 的用法

coalesce

colaesce 返回参数中第一个第一个非空表达式
coalesce(a,b,c) 如果a不为null，则返回a；同理a如果为null，返回b；

with rollup

with rollup 用于在进行group by 进行分组查询后，对分组数据进行汇总的功能。

示例：
请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况

select 
    coalesce(date_format(submit_time,'%Y%m'),'2021汇总') as submit_month,
    count(submit_time) as month_q_cnt,
    round(count(distinct submit_time)/max(day(last_day(submit_time))),3) as avg_day_q_cnt
from practice_record
where score is not null and year(submit_time)=2021
group by (date_format(submit_time,'%Y%m')) with rollup