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

select ui.uid,
count(if(start_time is not null and score is null,1,null)) as incomplete_cnt,
round(avg(if(start_time is not null and score is null,1,0)),3) as incomplete_rate
from user_info ui
left join exam_record er on ui.uid = er.uid

where case when
(select max(num) from
(select count(if(start_time is not null and score is null,1,null)) as num
from user_info ui
 join exam_record er on ui.uid = er.uid
where level=0
group by ui.uid)a)>2
then ui.uid in(select uid from user_info where level =0)
else ui.uid in (select uid from user_info where start_time is not null)
end

group by ui.uid
order by incomplete_rate

此题无思路 where还能搭配case when 这么玩