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

SELECT
    er.uid,
    SUM(IF (er.submit_time IS NULL, 1, 0)) AS incomplete_cnt,
    COUNT(er.submit_time) AS complete_cnt,
    GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(er.start_time, '%Y-%m-%d'), ':', ei.tag) ORDER BY er.start_time SEPARATOR ';') AS detail
FROM exam_record er
JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR (er.start_time) = '2021'
GROUP BY er.uid
HAVING incomplete_cnt BETWEEN 2 AND 4 AND complete_cnt >= 1
ORDER BY incomplete_cnt DESC

重点在于如何将detail列中的各信息正确放在一起。使用GROUP_CONCAT函数的时候需要注意：

1. 用DISINCT去重过滤掉重复的记录
2. 用ORDER BY子句可以将这些拼在一起的记录按start_time排序
3. 用SEPERATOR设置分割符号（默认是逗号）