题解 | #根据指定记录是否存在输出不同情况#
根据指定记录是否存在输出不同情况
http://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b
稍微有点复杂，分成是否有0级用户未完成数大于2的两种情况union all起来
1、先求出0级用户未完成数大于2的个数，大于0说明是第一种情况，只需要输出0级用户
需要注意的是0级用户可能没作答记录，用用户表左连接，只有start_time和submit_time都为null时才是没有作答记录的，未完成数记为0
start_time 不为null 且 submit_time 为null时才是未完成，记数1
2、0级用户未完成数大于2的个数，等于0说明是第二种情况，直接求作答用户的未完成情况即可
select t1.uid,sum(case when submit_time is null and start_time is null then 0 when submit_time is null and start_time is not null then 1 end) as incomplete_cnt, round(sum(case when submit_time is null and start_time is null then 0 when submit_time is null and start_time is not null then 1 end)/count(1),3) as incomplete_rate from user_info t1 left join exam_record t2 on t1.uid = t2.uid where level = 0 and 0 < ( select count(1) as cn from ( select t1.uid,count(1) as incomplete_cnt from user_info t1 join exam_record t2 on t1.uid = t2.uid where level = 0 and submit_time is null group by t1.uid having count(1) > 2 ) t ) group by t1.uid union all select uid,sum(if(submit_time is null,1,0)) as incomplete_cnt, round(sum(if(submit_time is null,1,0))/count(1),3) as incomplete_rate from exam_record where 0 = ( select count(1) as cn from ( select t1.uid,count(1) as incomplete_cnt from user_info t1 join exam_record t2 on t1.uid = t2.uid where level = 0 and submit_time is null group by t1.uid having count(1) > 2 ) t ) group by uid order by incomplete_rate
查看10道真题和解析