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

一、知识点

1.子查询tb

目标：计算出未完成的试卷总数

1. 对exam_id进行分组
2. 使用sum和case when分条件计算总数

sum 和case when 是记数的好搭档，它和sum if的区别就是，它支持更多的查询条件

2.最外层select

根据题目要求即可

注意：记得distinct exam_id，不然就会求出一堆exam_id对应的相同的结果！

select
    distinct exam_id,
    incomplete_cnt,
    round((incomplete_cnt / total),3) as complete_rate
from
    exam_record
    inner join (
        select
            distinct exam_id,
            sum(
                case
                    when start_time is not null
                    and submit_time is null then 1
                    else null
                end
            ) as incomplete_cnt,
            count(exam_id) as total
        from
            exam_record
        group by
            exam_id
    ) as tb using (exam_id)
where submit_time is null
order by exam_id