题解 | #根据指定记录是否存在输出不同情况#
根据指定记录是否存在输出不同情况
https://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b
自己做题太急躁了 总是审题出错 落掉题目的好多信息
with incom_cnt as (
select
uid,
(count(start_time) - count(score)) as incomplete_cnt,
ifnull(round(((count(start_time) - count(score))) / count(start_time), 3),0) as incomplete_rate,
level,count(start_time) as total_cnt
from
exam_record
right join user_info using(uid)
group by
uid
)
select
uid,
incomplete_cnt,
round(incomplete_rate, 3) as incomplete_rate
from
incom_cnt
where
exists(
select
uid
from
incom_cnt
where
level = 0
and incomplete_cnt > 2
)
and level = 0
union
select
uid,
incomplete_cnt,
round(incomplete_rate, 3) as incomplete_rate
from
incom_cnt
where
not exists(
select
uid
from
incom_cnt
where
level = 0
and incomplete_cnt > 2
) and total_cnt>0
order by
incomplete_rate