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

SELECT 
    a.uid,
    COUNT(IF(submit_time IS NULL,uid,NULL)) AS incomplete_cnt,
    COUNT(IF(submit_time IS NOT NULL,uid,NULL)) AS complete_cnt,
    GROUP_CONCAT(DISTINCT CONCAT(DATE(a.start_time),':',b.tag) ORDER BY DATE(a.start_time),b.tag SEPARATOR ';') AS detail # 这里直接连start_time就行，因为要求是作答过的
FROM exam_record a
JOIN examination_info b USING(exam_id)
WHERE YEAR(a.start_time) = '2021'
GROUP BY a.uid
HAVING incomplete_cnt >1 AND incomplete_cnt < 5 AND complete_cnt >= 1
ORDER BY a.uid DESC;