sql聚合分组查询-分组查询。涉及到coalesce函数、with rollup的使用

知识

coalesce

https://blog.csdn.net/yilulvxing/article/details/86595725
COALESCE是一个函数，coalesce (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。
SQL实例

select coalesce(success_cnt, 1) from tableA

当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。

select coalesce(success_cnt,period,1) from tableA

当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。

with rollup

https://blog.csdn.net/qq_31960623/article/details/115917641
使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。

last_day

last_day()返回参数日期的最后一天

MySQL中group_concat函数用法：

https://www.cnblogs.com/goloving/p/13942595.html

MySQL拼接函数CONCAT的用法

https://zhuanlan.zhihu.com/p/337866276

例题1

https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    # 以下字段前都加上 “t.” 都OK.
    coalesce(year_mon, '2021汇总') as submit_month,
    count(question_id) as month_q_cnt,
    round(count(question_id)/max(days_month),3) as avg_day_cnt
from 
    (select 
         question_id,
         dayofmonth(last_day(submit_time)) as days_month,
         date_format(submit_time,"%Y%m") as year_mon
     FROM practice_record
     WHERE year(submit_time)=2021) as t
group by year_mon # t.year_mon
with rollup;

例题2

https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    uid,
    sum(if(submit_time is null, 1, 0)) as incomplete_cnt
    sum(if(submit_time is null, 0, 1)) as complete_cnt
    group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';') as detail
from 
    exam_record er 
    join 
    examination_info ei 
    on er.exam_id = ei.exam_id
where YEAR(start_time) = 2021 
group by uid
having 
    incomplete_cnt>1
    and incomplete_cnt<5
    and complete_cnt >= 1
order by 
    incomplete_cnt desc