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

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