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

with t1 as ( -- 形成一张大表，包含所有uid的计算要素
select uid,count(t.start_time) as num,
case when count(t.start_time)=0 then 0 else count(submit_time is null or null) end as incomplete_cnt
from user_info left join exam_record t using(uid)
group by uid)

SELECT uid, incomplete_cnt,ifnull(round(incomplete_cnt/num,3),round(0,3)) as incomplete_rate from t1
where -- 关键在于条件中使用case when来控制uid
case when  (select count(1) from t1 join user_info using(uid) where incomplete_cnt>2 and level=0 ) >0
            then uid in (select uid from user_info where level=0)
            else uid in (select uid from exam_record ) end
order by incomplete_rate

关键点在于思路
1、先构建大表，然后根据判断中使用 case when 来控制uid
2、where 中跟case when