sql其他常用操作之空值处理

题目1

https://www.nowcoder.com/practice/69fc2b1df4144c0991e4b8280d8aba27?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

先以子表写法：

select 
    exam_id,
    sum(incomplete) as incomplete_cnt,
    round(sum(incomplete) / count(1), 3)  as incomplete_rate
from 
    (select exam_id, if(score is null, 1, 0) as incomplete
     from exam_record
    ) as t
group by exam_id
having incomplete_cnt >= 1

再根据子表写法的思路改写,直接select一步到位：

select 
    exam_id,
    sum(if(score is null, 1, 0)) as incomplete_cnt,
    #sum(case when score is null then 1 else 0 end) as incomplete_cnt, 
    round(sum(if(score is null, 1, 0)) / count(start_time), 3) as incomplete_rate 
from exam_record
group by exam_id
having incomplete_cnt >= 1

题2

https://www.nowcoder.com/practice/bb474c6cbd77478fb6d9fc86934d0ebb?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    uid,
    round(avg(new_score), 0) as avg_score,
    round(avg(cost_time), 1) as avg_time_took
from 
    (select
         er.uid,
         if(score is not null, score, 0) as new_score,
         if(submit_time is not null, TIMESTAMPDIFF(minute, start_time, submit_time), duration) as cost_time
     from 
         user_info join exam_record as er using(uid) 
         join examination_info using(exam_id)
     where  level=0 and difficulty="hard"
    ) as new_table
group by uid

再利用子表的思路，整理一下，实现不用子表的直接select一步到位的sql代码:

select 
    uid,
    round(avg(if(score is not null, score, 0)), 0) as avg_score,
    round(avg(if(submit_time is not null, TIMESTAMPDIFF(minute, start_time, submit_time), duration)), 1) as avg_time_took
from 
    user_info join exam_record as er using(uid) 
    join examination_info using(exam_id)
where  level=0 and difficulty="hard"
group by uid