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

SELECT 
	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) SEPARATOR';') detail
FROM 
	exam_record a JOIN examination_info b ON a.exam_id = b.exam_id
WHERE YEAR(start_time)='2021'
GROUP BY uid
HAVING
	incomplete_cnt > 1
	AND incomplete_cnt < 5
	and complete_cnt >= 1
ORDER BY incomplete_cnt DESC;