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

select distinct
		if(q.max_value>2,q.uid,qq.uid) as uid,
		if(q.max_value>2,q.incomplete_cnt,qq.incomplete_cnt) as incomplete_cnt,
		if(q.max_value>2,if(q.incomplete_rate is null, 0, q.incomplete_rate),if(qq.incomplete_rate is null,0,qq.incomplete_rate)) as incomplete_rate
from
(
select ui.uid,
		count(if(submit_time is null and start_time is not null,1,null)) as incomplete_cnt,
		round(count(if(submit_time is null and start_time is not null,1,null)) /count(start_time),3) as incomplete_rate,
		max(count(if(submit_time is null and start_time is not null,1,null))) over () as max_value
from exam_record er right join user_info ui
on er.uid = ui.uid
where `level` = 0
group by ui.uid
) q,
(
select ui.uid,
		count(if(submit_time is null and start_time is not null,1,null)) as incomplete_cnt,
		round(count(if(submit_time is null and start_time is not null,1,null)) /count(start_time),3) as incomplete_rate
from exam_record er right join user_info ui
on er.uid = ui.uid
where start_time is not null#是有作答记录
group by ui.uid
) qq
order by incomplete_rate;