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

select
    uid,
    sum(if (submit_time  is null, 1, 0)) as incomplete_cnt,
    sum(if (submit_time  is not null, 1, 0)) as complete_cnt,
    group_concat(distinct concat_ws(':',date(start_time),tag) order by start_time separator ';') as detail
from
    exam_record er
    INNER  join examination_info ei on er.exam_id = ei.exam_id
WHERE
    year (start_time) = 2021
GROUP BY
    uid
HAVING
    complete_cnt >= 1
    AND incomplete_cnt > 1
    AND incomplete_cnt < 5
ORDER BY
    incomplete_cnt desc