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

select uid, 
    count(if(submit_time is null, 1, null)) incomplete_cnt,
    count(if(submit_time is null, null, 1)) complete_cnt,
    group_concat(distinct concat_ws(':',date_format(start_time, '%Y-%m-%d'), tag) separator ';') detail
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
where year(start_time) = '2021'
group by uid
having count(if(submit_time is null, 1, null))>1
    and count(if(submit_time is null, 1, null))<5
    and count(if(submit_time is null, null, 1))>=1
order by count(if(submit_time is null, 1, null)) desc;