题解 | 未完成试卷数大于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(DATE_FORMAT(start_time, '%Y-%m-%d'), ':', tag)
        ORDER BY start_time
        SEPARATOR ';'
    ) AS detail
FROM exam_record er
LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR(start_time) = 2021
    AND uid IN (
        SELECT uid 
        FROM exam_record
        WHERE YEAR(start_time) = 2021
        GROUP BY uid
        HAVING 
            COUNT(IF(submit_time IS NOT NULL, 1, NULL)) >= 1  -- 完成数至少为1
            AND COUNT(IF(submit_time IS NULL, 1, NULL)) < 5   -- 未完成数小于5
    )
GROUP BY uid
HAVING COUNT(IF(submit_time IS NULL, 1, NULL)) > 1  -- 未完成数大于1
ORDER BY incomplete_cnt DESC;