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

select u.uid,
if(incomplete_cnt is null,0,incomplete_cnt) as incomplete_cnt,
round(if(incomplete_cnt is null,0,incomplete_cnt/total_cnt),3) as incomplete_rate
from user_info u left join 

(SELECT uid,
sum(IF(score is null,1,0)) as incomplete_cnt,
count(*) as total_cnt
FROM exam_record 
group by uid) all_user_table
ON u.uid=all_user_table.uid

left join 

(SELECT u.uid
from user_info u JOIN exam_record e on u.uid=e.uid
WHERE u.level=0
group by uid
HAVING sum(if (score is null,1,0))>2) level_zero
on 1=1
where (level_zero.uid is not null and level =0)
or (level_zero.uid is null and all_user_table.uid is not NULL)
order by incomplete_rate;