IF选择两个表 | #根据指定记录是否存在输出不同情况#
根据指定记录是否存在输出不同情况
https://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b
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等价。
