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

SELECT
    r.uid,
    SUM(CASE WHEN r.submit_time IS NULL THEN 1 ELSE 0 END) AS incomplete_cnt,
    SUM(CASE WHEN r.submit_time IS NOT NULL THEN 1 ELSE 0 END) AS complete_cnt,
    GROUP_CONCAT(DISTINCT CONCAT(DATE(r.start_time), ':', e.tag) ORDER BY r.start_time ASC SEPARATOR ';') AS detail
FROM
    test.exam_record r
JOIN
    test.examination_info e ON r.exam_id = e.exam_id
WHERE
    YEAR(r.start_time) = 2021
GROUP BY
    r.uid
HAVING
    incomplete_cnt > 1
    AND complete_cnt >= 1
    AND incomplete_cnt < 5
ORDER BY
    incomplete_cnt DESC;