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

SELECT er.uid AS uid, SUM(IF(er.submit_time IS NULL,1,0)) AS incomplete_cnt, SUM(IF(er.submit_time IS NOT NULL,1,0)) AS complete_cnt, GROUP_CONCAT(DISTINCT CONCAT( DATE_FORMAT( er.start_time,'%Y-%m-%d'),':',ei.tag) separator ";") AS detail

FROM exam_record er LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id WHERE YEAR(er.start_time)='2021' GROUP BY er.uid HAVING incomplete_cnt<5 AND complete_cnt>=1 AND incomplete_cnt>1 ORDER BY incomplete_cnt DESC;

#看答案写出来的。。。。卧槽。有点难啊。