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

# 查询出有未完成状态的试卷id
select distinct exam_id
from exam_record
where submit_time is null
# 查询有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate
select exam_id, sum(if(submit_time is null, 1, 0)) incomplete_cnt,
round(avg(submit_time is null),3) incomplete_rate
from exam_record
where exam_id in (
    select distinct exam_id
    from exam_record
    where submit_time is null
)
group by exam_id;

思路：

（1）先查询出有未完成状态试卷的试卷id

（2）以试卷id为分组依据来计算各试卷的未完成数和未完成率，应当善用sum()和avg()嵌套布尔值