题解 | #根据指定记录是否存在输出不同情况#
根据指定记录是否存在输出不同情况
https://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b
select uid,incomplete_cnt,incomplete_rate from (select uid,incomplete_cnt,incomplete_rate,if(ca=1,if(con>=1,1,0),if(con<=1,1,0)) as condi from ( select uid,max(cnt) as "incomplete_cnt",max(rate) as "incomplete_rate",round(avg(mark),0) as "con",max(if((round(avg(mark),0)>=1 and max(cnt)>2),1,0))over() as "ca" from (select uid,count(start_time)-count(submit_time) as "cnt",round((count(start_time)-count(submit_time))/count(start_time),3) as "rate",0 as "mark" from exam_record group by uid union all select uid,0 as "cnt",0.000 as "rate",2 as "mark" from user_info where level=0) a group by uid) t ) f where condi=1 order by incomplete_rate
思路:
1.构成一个table (select uid,0 as "cnt",0.000 as "rate",2 as "mark" from user_info where level=0)
该表中只包含等级为0的用户,未完成试卷数量和未完成率都默认为0,mark为2标记为0水平用户
2.统计已有作答记录的各个用户未完成试卷数量和为完成率,mark为0标记为有作答记录用户
3.union all 联合上述两个table,然后进行分组聚合,未完成数量和未完成率取max,mark取平均值
经过上述操作后,此时mark有3个取值,0--有作答记录但水平不为0的用户,1--有作答记录且水平为0的用户,2--没有作答记录但水平 为0的用户
4.判断是条件一输出还是条件二输出:统计mark>=1的数据中是否有incomplete_cnt>2即是否有水平为0的用户的为完成作答数>2
max(if((round(avg(mark),0)>=1 and max(cnt)>2),1,0))over() as "ca" 我通过开窗函数将此指标放在表中
5.如果是条件一(ca指标的值为1),查询mark>=1的数据;如果是条件二(ca指标的值为0),查询mark<=1的数据
if(ca=1,if(con>=1,1,0),if(con<=1,1,0)) 如果这个值为1说明是符合要求的数据
