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

with a as
(
select uid,level,sum(case when start_time is not null and submit_time is null then 1 else 0 end) as incomplete_cnt,count(start_time) as cnt,ifnull(round(sum(case when start_time is not null and submit_time is null then 1 else 0 end)/count(start_time),3),0)  as incomplete_rate
from exam_record
right join user_info
using (uid)
group by uid
)
select uid,incomplete_cnt,incomplete_rate from a
where exists
(select uid from a
where incomplete_cnt>2 and level=0) and level=0 
union all
select uid,incomplete_cnt,incomplete_rate from a
where not exists
(select uid from a
where incomplete_cnt>2 and level=0)
and cnt>0
order by incomplete_rate