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

select 
    uid
    , sum(if(a.submit_time is null, 1, 0)) incomplete_cnt
    , sum(if(a.submit_time is null, 0, 1)) complete_cnt
    , group_concat(distinct concat(date_format(a.start_time, '%Y-%m-%d'), ':', b.tag) order by a.start_time separator ';') detail
from 
    exam_record a
    left join 
    examination_info b
    on a.exam_id = b.exam_id
where year(start_time) = '2021'
group by 
    uid
having 
    incomplete_cnt > 1
    and complete_cnt >= 1
    and incomplete_cnt < 5
order by 
    incomplete_cnt desc
;