题解 | #根据指定记录是否存在输出不同情况#
根据指定记录是否存在输出不同情况
https://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b
with data_all_level as ( select level, uid , sum(if(submit_time is null and start_time is not null, 1, 0)) incomplete_cnt , round(sum(if(submit_time is null and start_time is not null, 1, 0)) / count(if(start_time is null, 0, 1)), 3) as incomplete_rate , count(start_time) total_cnt from exam_record right join user_info using (uid) group by level, uid ), uid_level_0 as ( select sum(ifnull(uid,0)) level_0_cnt from data_all_level where level = 0 and incomplete_cnt > 2 group by incomplete_cnt ) select uid, incomplete_cnt, ifnull(incomplete_rate, 0) from data_all_level where (if((select level_0_cnt from uid_level_0) > 0 ,(uid in (select uid from data_all_level where level = 0)) ,(uid in (select uid from data_all_level where total_cnt > 0)))) order by incomplete_rate
昨天一直没做对，直到今天看了一位大佬的答案，才发现自己题没读清楚
如果没有符合条件的0级用户，要求输出的是所有有做过题的用户的数据
这样加了total_cnt 这个筛选条件之后结果才正确了
