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

1. 每个用户试卷未完成数和未完成率（保留3位小数）

第一种：思路清晰版

select 
    uid,        #包含有、无作答记录的用户，无作答记录的三个指标均设为0
    ifnull(incomplete_cnt, 0) incomplete_cnt, 
    ifnull(did_cnt, 0) did_cnt,
    ifnull(incomplete_rate, 0.000) incomplete_rate, 
    level
from user_info a 
left join (
    select 
        uid,    #均为有作答记录的用户
        sum(if(submit_time is null, 1, 0)) incomplete_cnt,
        count(start_time) did_cnt,
        round(sum(if(submit_time is null, 1, 0))/count(start_time),3) incomplete_rate
    from exam_record
    group by uid    
)t using(uid) 

第二种：缩减版

select 
    uid, level,
    count(start_time) - count(submit_time) incomplete_cnt,  #此时sum(if(submit_time is null, 1, 0))不行，因为合并了user_info和exam_record，可能存在无作答记录的用户
    count(submit_time) complete_cnt,
    count(start_time) did_cnt,
    round(if(count(start_time)!=0, (count(start_time)-count(submit_time))/count(start_time), 0), 3) incomplete_rate    #未作答过的填0.000
from user_info a left join exam_record b using(uid)
group by uid 

2.将上述结果作为临时表，用where (not) exists和UNION 合并两种情况的查询结果。

# 所有用户（包括有无作答记录的）的未完成数、完成数、作答数。有作答记录但未完成数为0的did_cnt>0，无作答记录的did_cnt为0
with t as (
    select 
        uid, level,
        count(start_time) - count(submit_time) incomplete_cnt,  #此时sum(if(submit_time is null, 1, 0))不行，因为合并了user_info和exam_record，可能存在无作答记录的用户
        count(submit_time) complete_cnt,
        count(start_time) did_cnt,
        round(if(count(start_time)!=0, (count(start_time)-count(submit_time))/count(start_time), 0), 3) incomplete_rate    #未作答过的填0.000
    from user_info a left join exam_record b using(uid)
    group by uid 
)

select uid, incomplete_cnt, incomplete_rate from t
where exists (select uid from t where incomplete_cnt>2 and level=0)
and level=0     #第一种情况，输出每个0级用户的试卷未完成数和未完成率
UNION 
select uid, incomplete_cnt, incomplete_rate from t
where not exists (select uid from t where incomplete_cnt>2 and level=0)
and did_cnt>0   #第二种情况，输出所有有作答记录的用户的这两个指标
order by incomplete_rate