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

with a as
(select u.uid,count(case when submit_time is null and start_time is not null then 1 else null end) as cnt
from user_info u 
left join exam_record e on u.uid=e.uid
where level =0
group by u.uid)

select u.uid,count(case when submit_time is null and start_time is not null then 1 else null end) as incomplete_cnt,round(ifnull(count(case when submit_time is null and start_time is not null then 1 else null end)/count(start_time),0.000),3) as incomplete_rate
from user_info u 
left join exam_record e on u.uid=e.uid
left join a on u.uid=a.uid
where (case when (select max(cnt) from a)>2 then  level=0 else start_time is not null end )
group by u.uid
order by incomplete_rate

三刷 相对简单