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

SELECT
    uid,
    sum(

        IF (
            score IS NULL
            OR submit_time IS NULL,
            1,
            0
        )
    ) incomplete_cnt,
    sum(

        IF (
            score IS NULL
            OR submit_time IS NULL,
            0,
            1
        )
    ) complete_cnt,
    group_concat(
        DISTINCT concat(
            date_format(start_time, "%Y-%m-%d"),
            ":",
            tag
        )
        ORDER BY
            start_time SEPARATOR ";"
    ) detail
FROM
    exam_record a
JOIN examination_info b USING (exam_id)
WHERE
    YEAR (start_time) = 2021
GROUP BY
    uid
HAVING
    incomplete_cnt BETWEEN 2
AND 4
AND complete_cnt >= 1
ORDER BY
    incomplete_cnt DESC