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

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