题解 | #根据指定记录是否存在输出不同情况#

# 分条件输出时，使用exists来限制条件输出，同时使用union来连接两个查询语句
with t1 as
(
    select er.*, ui.uid uuid, level 
    from exam_record er
    right join user_info ui
    on er.uid=ui.uid
)

select uuid, round(sum(score is null and start_time is not null),3) incomplete_cnt, round(avg(score is null and start_time is not null),3) incomplete_rate
from t1
where exists (
    select uuid 
    from t1
    where level = 0
    group by uuid
    having sum(score is null) > 2
)  and level = 0
group by uuid
union
select  uuid, round(sum(score is null and start_time is not null),3) incomplete_cnt, round(avg(score is null and start_time is not null),3) incomplete_rate
from t1
where not exists (
    select uuid 
    from t1
    where level = 0
    group by uuid
    having sum(score is null) > 2
)  
group by uuid
having count(start_time)>0
order by incomplete_rate;

解题要注意的是两种输出情形：（1）输出全部0级用户的未完成信息；（2）输出所有作答记录表中有记录的用户的未完成信息。