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

with cte as (
select uid,start_time,submit_time,tag,concat(date(start_time),':',tag) as deta
from exam_record left join examination_info using(exam_id)
where year(start_time)='2021' )
select uid,count(if(submit_time is null,1,null)) as incomplete_cnt,
           count(submit_time)                    as complete_cnt,
group_concat(distinct deta ORDER BY date(start_time),tag separator ';') as detail
from cte
group by uid
having complete_cnt>=1 and incomplete_cnt>1 and incomplete_cnt<5
order by incomplete_cnt desc