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

select uid,sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,sum(case when submit_time is not null then 1 else 0 end) complete_cnt,group_concat(distinct concat(left(start_time,10),":",tag) separator ';')
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+complete_cnt>0 and incomplete_cnt<5 and incomplete_cnt>1
order by incomplete_cnt desc