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

with temp as(
select t.uid,level,sum(start_time is not null and submit_time is null) as incomplete_cnt,
        round(sum(start_time is not null and submit_time is null)/count(1),3) as incomplete_rate,
        count(exam_id) as num
    from user_info t
    left join exam_record t1
    using(uid)
    group by t.uid)

select uid,incomplete_cnt,incomplete_rate
from temp
where exists(select uid from temp where incomplete_cnt > 2 and level = 0) and level = 0
union all
select uid,incomplete_cnt,incomplete_rate
from temp
where not EXISTS (
    select uid from temp where level=0 and incomplete_cnt>2
) and num>0
order by incomplete_rate