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

select e1.uid, sum(if(submit_time is null,1,0)) as incomplete_cnt, sum(if(submit_time is null,0,1)) as complete_cnt, GROUP_CONCAT(distinct concat(date_format(start_time,'%Y-%m-%d'),':',tag) order by start_time SEPARATOR ";") as detail from exam_record as e1 left join examination_info as e2 on e1.exam_id=e2.exam_id where year(start_time)='2021' group by e1.uid having complete_cnt >= 1 and incomplete_cnt<5 and incomplete_cnt>1 order by incomplete_cnt desc;