题解 145 | #统计有未完成状态的试卷的未完成数#

【场景】：存在空值

【分类】：空值处理、if 函数、select条件语句

分析思路

难点：

1.查询结果一个有条件限制一个没有条件限制，怎么查询？两种方法

一是使用where条件分别查询，最后做表连接（列合并）；

二使用select条件语句，在select查询结果中进行条件判断。

（1）统计作答记录

• [使用]：count ... group by exam_id

（2）统计作答未完成记录

• [条件]：score is null

（3）统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate，并按exam_id升序

• [使用]： incomplete_cnt <> 0 ；order by exam_id

求解代码

方法一：

with子句 + where条件

with
    main as(
        #统计作答记录
        select 
            exam_id,
            count(*) as complete_rate
        from exam_record
        group by exam_id
    )
    ,attr as(
        #统计作答未完成记录
        select 
            exam_id,
            count(*) as incomplete_cnt
        from exam_record
        where score is null
        group by exam_id
    ) 

#统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate
select 
    exam_id,
    incomplete_cnt,
    round(incomplete_cnt/complete_rate, 3) as complete_rate
from main
join attr using(exam_id)
where incomplete_cnt <> 0
order by exam_id

方法二：

with子句 + select条件语句

with
    main as(
        #统计作答记录
        select 
            exam_id,
            count(*) as complete_rate,
            count(if(score is null,1,null)) as incomplete_cnt
        from exam_record
        group by exam_id
    ) 

#统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate
select 
    exam_id,
    incomplete_cnt,
    round(incomplete_cnt/complete_rate, 3) as complete_rate
from main
where incomplete_cnt <> 0
order by exam_id

方法三

#统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate
select 
    exam_id,
    sum(if(score is null,1,0)) as complete_rate,
    round(sum(if(score is null,1,0))/count(*), 3) as complete_rate
from exam_record
group by exam_id having complete_rate <> 0
order by exam_id