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

with xx as
(select user_info.uid,incomplete_cnt,incomplete_rate,level
from(
    select uid,sum(if(score is null,1,0)) incomplete_cnt,
    round(sum(if(score is null,1,0))/count(uid) ,3) incomplete_rate
    from exam_record
    group by uid
    ) t1
right join  user_info
on user_info.uid = t1.uid
)

(select uid,ifnull(incomplete_cnt,0) incomplete_cnt,ifnull(incomplete_rate,0) incomplete_rate
from xx
where  exists (select uid from xx where level=0 and incomplete_cnt>2) and level=0
)
union all
(select uid, incomplete_cnt, incomplete_rate
from xx
where  not exists (select uid from xx where level=0 and incomplete_cnt>2) and incomplete_cnt is not null
 )
order by incomplete_rate

exists用法：用于子查询中，满足条件返回TRUE