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

select uid, count(if(submit_time is null,1,null)) incomplete_cnt,
count(submit_time) complete_cnt,
group_concat(distinct detail_i separator ';') detail
from(
    select er.uid, start_time, submit_time, er.exam_id, tag, concat(date(start_time),':',tag) detail_i
    from exam_record er
    left join examination_info ei
    on er.exam_id=ei.exam_id
    where uid in (            
        select uid            # 该子查询用于查询有效用户的id
        from exam_record
        group by uid
        having count(*)>1 and count(if(submit_time is null,1,null))<5
    )
) k1
where year(start_time)='2021'   # 过滤出各有效用户在2021年的答题记录
group by uid
having count(if(submit_time is null,1,null)) > 1
order by incomplete_cnt desc;

使用count(if(submit_time is null,1,null))，来计算未完成数量，因为count()函数计数时自动忽略null值