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

SELECT 
    uid,
    SUM(incomplete) AS incomplete_cnt,
    SUM(complete) AS complete_cnt,
    GROUP_CONCAT(DISTINCT detail SEPARATOR ';') AS detail
FROM
    (
        SELECT
            uid,
            CONCAT (DATE (start_time), ':', tag) AS detail,
            if (submit_time is not null, 1, 0) as complete,
            if (submit_time is null, 1, 0) as incomplete
        FROM
            exam_record er
            JOIN examination_info ei ON er.exam_id = ei.exam_id
        WHERE
            YEAR (start_time) = 2021
    ) as new_table
GROUP BY
    uid
HAVING incomplete_cnt>1 AND incomplete_cnt<5 AND complete_cnt>=1
ORDER BY incomplete_cnt DESC;

GROUP_CONCAT用法