题解 | #根据指定记录是否存在输出不同情况#

# 我的写法挺笨的 将就着看吧
# 当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）  若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。
# 0级用户的uid
with A as (
select uid from user_info where level = 0
)
# 所有有作答记录的用户的试卷未完成数和未完成率 incomplete_cnt	incomplete_rate
,B as (
select uid
,count(start_time)-count(submit_time)incomplete_cnt
,round((count(start_time)-count(submit_time))/count(start_time),3)incomplete_rate
from exam_record
group by 1
)
# 查看0级用户未完成试卷数大于2 有多少个
,C as 
(select count(*)h
from A
join B using(uid)
where incomplete_cnt > 2)
# 连表 用case when h=0 then 这里是第二种情况 所有有作答记录的用户的这两个指标
,D as 
(select case when h=0 then uid end uid
,case when h=0 then incomplete_cnt end incomplete_cnt
,case when h=0 then incomplete_rate end incomplete_rate
from B
join C)
# 连表 用case when h>0 then 这里是第一种情况 每个0级用户的试卷未完成数和未完成率
,E as 
(select case when h>0 then uid end uid
,case when h>0 then ifnull(incomplete_cnt,0) end incomplete_cnt
,case when h>0 then ifnull(incomplete_rate,0) end incomplete_rate
from A
left join B using(uid)
join C)
# 两种情况union all 再去空
select * from
(select * from D
union all
select * from E)z
where uid is not null
order by incomplete_rate