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

select uid
        ,count(case when submit_time is null then 1 else null end) AS incomplete_cnt
        ,count(case when submit_time is not null then 1 else null end) AS complete_cnt
        ,group_concat(distinct concat(date(start_time),':',tag) separator ';') AS detail
from(
    select uid
            ,submit_time
            ,start_time
            ,tag
    from exam_record er
    left join examination_info ei
    on er.exam_id = ei.exam_id
    where year(start_time) = 2021
)exam
group by uid
having complete_cnt >= 1 and incomplete_cnt between 2 and 4
order by incomplete_cnt desc