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

SELECT uid, incomplete_cnt, complete_cnt, (
	SELECT GROUP_CONCAT(DISTINCT DATE(start_time),':',tag ORDER BY start_time SEPARATOR ';')
	FROM exam_record 
	JOIN examination_info USING(exam_id) 
	WHERE YEAR(start_time)=2021 AND uid = complete_t.uid
	) AS detail
FROM (SELECT
	uid, COUNT(submit_time) AS complete_cnt
	FROM exam_record
    WHERE YEAR(start_time)=2021
	GROUP BY uid
) AS complete_t
LEFT JOIN (
	SELECT uid, COUNT(start_time) AS incomplete_cnt
	FROM exam_record
    WHERE submit_time IS NULL AND YEAR(start_time)=2021
	GROUP BY uid
) AS incomplete_t USING(uid)
WHERE complete_cnt >= 1 AND incomplete_cnt < 5 AND incomplete_cnt > 1