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

select
    a.uid,
    sum(case when submit_time is null then 1 else 0 end) as incomplete_cnt,
    sum(case when submit_time is not null then 1 else 0 end) as complete_cnt,
    group_concat(distinct concat_ws(':',date(start_time),tag) order by start_time separator';') as detail
from 
    exam_record a 
left join
    examination_info b on a.exam_id = b.exam_id
where 
    year(a.start_time) = 2021
group by 
    a.uid
having 
    sum(case when submit_time is not null then 1 else 0 end) >=1 
    and 
    sum(case when submit_time is null then 1 else 0 end) < 5
    and
    sum(case when submit_time is null then 1 else 0 end) > 1
order by 
    incomplete_cnt desc