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

SELECT e1.uid, COUNT(start_time) - COUNT(submit_time) incomplete_cnt, 
    COUNT(submit_time) complete_cnt,
    GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(start_time,'%Y-%m-%d'),':',tag) SEPARATOR ';')
FROM exam_record e1 
LEFT JOIN 
    (SELECT uid, COUNT(submit_time) complete_cnt
    FROM exam_record e1 GROUP BY uid) e2 
ON e1.uid = e2.uid
LEFT JOIN  examination_info e3
ON e1.exam_id = e3.exam_id
WHERE DATE_FORMAT(start_time,'%Y') = 2021
GROUP BY uid
HAVING COUNT(1) - COUNT(submit_time) BETWEEN 2 AND 4 
ORDER BY incomplete_cnt DESC

新知识GROUP_CONCAT(拼接列 SEPARATOR '你需要的分隔符') + GROUP BY