IF选择两个表 | #根据指定记录是否存在输出不同情况#

with t1 as
(select u.uid uid, sum(case when start_time and submit_time is null then 1 else 0 end) incomplete_cnt, round(sum(case when start_time and submit_time is null then 1 else 0 end)/count(1), 3) incomplete_rate
from user_info u left join exam_record e
on u.uid = e.uid
where level = 0
group by uid order by incomplete_rate)

, t2 as
(select e.uid uid, sum(case when start_time and submit_time is null then 1 else 0 end) incomplete_cnt, round(sum(case when start_time and submit_time is null then 1 else 0 end)/count(1), 3) incomplete_rate
from exam_record e left join user_info u
on e.uid = u.uid
group by uid order by incomplete_rate)

select if((select max(incomplete_cnt) from t1) > 2, t1.uid, t2.uid) uid, max(if((select max(incomplete_cnt) from t1) > 2, t1.incomplete_cnt, t2.incomplete_cnt)) incomplete_cnt, max(if((select max(incomplete_cnt) from t1) > 2, t1.incomplete_rate, t2.incomplete_rate)) incomplete_rate
from t1, t2
group by uid order by incomplete_rate;

由于分组的数据相同，此处使用MAX、MIN、AVG等价。