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

-- 1.存在0级用户未完成试卷数大于2，输出每个0级别用户的 试卷未完成数和未完成率
-- 2.若不存在，则输出所有作答记录的用户的这两个指标。
-- 字段：uid、incomplete_cnt、incomplete_rate
-- tb1：链接表
with tb1 as(
    select uid,level,start_time,submit_time,score
    from exam_record right join user_info using(uid)
),
-- tb2：求取总量用户
tb2 as(
    select uid,round(count(if(submit_time is null,start_time,null)),3) as  incomplete_cnt,
    round(count(if(submit_time is null,start_time,null))/count(start_time),3) as incomplete_rate,
    max(level) as level,
    count(submit_time) as complete_cnt
    from tb1
    group by uid
)

-- tb3：情况1（level=0，存在未完成数>2）只求取level=0
select uid,incomplete_cnt,round(if(incomplete_rate is not null,incomplete_rate,0),3) AS incomplete_rate
from tb2
WHERE EXISTS(SELECT uid FROM tb2 WHERE level='0' AND incomplete_cnt>2) #出现level=0且存在incomplete_cnt>2时
AND level='0' #输出level=0的用户未完成数和未完成率
UNION ALL 
-- 情况2（level=0，不存在 未完成数>2）输出有作答记录的用户的这两个指标。
select uid,incomplete_cnt,round(if(incomplete_rate is not null,incomplete_rate,0),3) AS incomplete_rate 
from tb2
where not exists(select uid from tb2 where level= '0' and incomplete_cnt>2)
AND  complete_cnt+incomplete_cnt >=1 -- 完成次数>1
# order by incomplete_rate;
ORDER BY incomplete_rate

# 注意：有作答记录 即 不管是否完成