题解 | #未完成试卷数大于1的有效用户#

真的是吐了，好难 知识点：group_concat( separator '') 多行内容转换成一行 having 用在分组后的筛选，where 分组前的筛选 还有就是条件的理解（重点是理解问题很难） 注意开始作答和完成作答不一样：在where year(start_time )='2021'中，使用submit_time会报错 知识点2：date() 取年月日

sum(if(submit_time is null,1,0)) as incomplete_cnt,
count(submit_time) as complete_cnt,
group_concat(distinct concat(date(start_time),':',tag) separator ';') as detail
from exam_record e
left join examination_info em
on  e.exam_id =em.exam_id 
where year(start_time )='2021'
group by uid
having complete_cnt >= 1 and incomplete_cnt <5 and incomplete_cnt>1  
order by incomplete_cnt desc