牛客SQL128——未完成试卷数大于1的有效用户

题目描述
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286

思路：
这道题难的地方是在detail如何进行拼接，
对一条数据进行拼接使用concat_ws(‘:’,date(start_time),tag)
对一组数据进行拼接使用group_concat(distinct concat_ws(‘:’,date(start_time),tag),seperate ‘;’)
对聚合数据进行选择不能用where要用having

select uid,(count(start_time)-count(submit_time)) as incomplete_cnt,count(submit_time) as complete_cnt,
group_concat(distinct concat_ws(':',date(start_time),tag) order by start_time separator ';') as detail

from exam_record as r join examination_info as e on r.exam_id=e.exam_id
where year(start_time)=2021
group by uid
having 1<incomplete_cnt and incomplete_cnt<5 and count(submit_time)>=1
order by incomplete_cnt desc