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

select uid,level,cnt_incom_0,cnt_incom,rate_incom,cnt_t,
max(cnt_incom_0) over() as max_cnt_incom_0 from(
select distinct a.uid,a.level,
count(if(a.level=0,b.id,null)) over(partition by a.uid)
- count(if(a.level=0,b.score,null)) over(partition by a.uid) as cnt_incom_0,
count(b.id) over(partition by a.uid)
- count(b.score) over(partition by a.uid) as cnt_incom,
count(b.id) over(partition by a.uid) as cnt_t,
round( (count(b.id) over(partition by a.uid)
        -count(b.score) over(partition by a.uid))
      /count(b.id) over(partition by a.uid) ,3) as rate_incom
from user_info a
left join exam_record b on a.uid=b.uid )c)d
where level>=0
and level<=(case when max_cnt_incom_0>2 then 0 else 100 end)
and cnt_t>=(case when max_cnt_incom_0>2 then 0 else 1 end)
order by rate_incom