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

select uid,
count(case when score is null then  1 end) incomplete_cnt,
count(case when score is not null then 1 end) complete_cnt,
group_concat(distinct concat_ws(':',DATE_FORMAT(start_time,'%Y-%m-%d'),tag) separator ';') detail
from exam_record a
left join examination_info b
on a.exam_id=b.exam_id
where YEAR(start_time)=2021
group by uid
having count(case when score is not null then 1 end)>=1
and count(case when score is  null then 1 end)<5
and count(case when score is null then 1 end)>1
order by count(case when score is not null then 1 end) desc