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

select uid,
# 用0，1分别记录未完成和已完成试卷
sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,
sum(case when submit_time is null then 0 else 1 end) complete_cnt,
group_concat(distinct concat_ws(":",date(start_time),tag) separator ';') detial
from exam_record r,examination_info i
where r.exam_id = i.exam_id and year(start_time) = 2021
group by uid
having complete_cnt >= 1 and incomplete_cnt < 5 and incomplete_cnt > 1
order by incomplete_cnt desc