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

select
uid
,incomplete_cnt
,complete_cnt
,detail
from
(
    select
    uid
    ,sum(case when submit_time is  null then 1 else 0 end)        incomplete_cnt
    ,sum(case when submit_time is not null then 1 else 0 end)     complete_cnt
    ,group_concat(distinct substring(start_time,1,10),':',tag order by start_time separator ';')  detail#字符串去重合并
    from exam_record t1 left join examination_info t2
    on t1.exam_id = t2.exam_id
    where substring(start_time,1,4) = '2021'
    group by 1
) table1
where incomplete_cnt < 5
and  incomplete_cnt > 1
and  complete_cnt >= 1
order by 2 desc