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

SELECT uid, count(IF(submit_time is null,1,NULL)) AS imcomplete_cnt,

sum(IF(submit_time is not null,1,0)) as complete_cnt, GROUP_CONCAT(DISTINCT concat_ws(':', date(start_time), tag) SEPARATOR ';') as detail

FROM exam_record JOIN examination_info using(exam_id) WHERE YEAR(start_time)='2021' GROUP BY uid

HAVING imcomplete_cnt>1 and imcomplete_cnt<5 and complete_cnt>=1

ORDER BY imcomplete_cnt DESC