这道题你难得无敌了 | #未完成试卷数大于1的有效用户#

SELECT
    uid,
    count(distinct case when start_time is not null and submit_time is null then r.id end) AS incomplete_cnt,
    count(distinct case when start_time is not null and submit_time is not null then r.id end) AS complete_cnt,
    GROUP_CONCAT(distinct CONCAT(DATE_FORMAT(CASE WHEN submit_time IS NOT NULL THEN submit_time ELSE start_time END, '%Y-%m-%d'), ':', i.tag) ORDER BY start_time SEPARATOR ';') AS detail
    
FROM
    exam_record r
JOIN
    examination_info i
ON
    r.exam_id = i.exam_id
WHERE
    YEAR(start_time) = '2021' 
GROUP BY
    uid
HAVING
    incomplete_cnt > 1 and incomplete_cnt < 5 and complete_cnt >= 1
ORDER BY
    incomplete_cnt DESC