题解 | 本题难点：Exists
根据指定记录是否存在输出不同情况
http://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b
-- 第一步 做出所有人的两个指标
with t 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 on t.uid = t1.uid
group by t.uid
)
-- 第二步 冲
select uid,incomplete_cnt,incomplete_rate
from t
where EXISTS (
select uid from t where level=0 and incomplete_cnt>2
) and level=0
union ALL
select uid,incomplete_cnt,incomplete_rate
from t
where not EXISTS (
select uid from t where level=0 and incomplete_cnt>2
) and num>0
order by incomplete_rate
腾讯开奖367人在聊