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

SELECT
uid,
COUNT(IF(submit_time IS NULL,1,NULL)) AS incomplete_cnt,
COUNT(IF(submit_time IS NOT NULL,1,NULL)) AS complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT_WS(':', DATE_FORMAT(start_time,'%Y-%m-%d'),tag) )
FROM exam_record er 

LEFT JOIN examination_info ei
ON er.exam_id = ei.exam_id

WHERE YEAR(start_time) = '2021'
GROUP BY uid
HAVING complete_cnt >= 1
AND incomplete_cnt BETWEEN 2 AND 4
ORDER BY incomplete_cnt DESC;