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

select * from 
(
select 
    t1.uid
    ,sum(t1.unsub_cnt) incomplete_cnt
    ,sum(t1.sub_cnt) complete_cnt
    ,group_concat(distinct concat_ws(':', date(start_time),tag) SEPARATOR ';') detail
from (
    select 
        er.*,
        ei.tag
    from (
        select
            uid,
            exam_id,
            0 as sub_cnt,
            case when submit_time is null then 1 else 0 end as unsub_cnt,
            start_time
        from exam_record
        union all
        select
            uid,
            exam_id,
            case when submit_time is not null then 1 else 0 end as sub_cnt,
            0 as unsub_cnt,
            start_time
        from exam_record
        ) er
    left join examination_info ei
    on er.exam_id=ei.exam_id
    where year(start_time)='2021'
    and tag is not null
) t1
group by t1.uid
) a
where incomplete_cnt>1
and incomplete_cnt<5
and complete_cnt>=1
order by incomplete_cnt desc