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

select exam_id,
(count(*)-count(submit_time)) as incomplete_cnt,
round(((count(start_time)-count(submit_time))/count(start_time)),3) as incomplete_rate
from exam_record
group by exam_id
having incomplete_cnt!= 0

知识点:这里用到了having这个聚合后筛选,注意这个筛选是筛选出想要的,这里有一个巧妙的地方就是,不需要再聚合之前把全部完成的筛选出去,只需要再聚合之后,把完成率为1的筛选出去即可.