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

with unfi as (
    select exam_id from exam_record where submit_time is null
),
unficnt as (# 未完成数量
    select exam_id, count(*) as ucnt from exam_record where submit_time is null
    group by exam_id
),
total as(
    select exam_id, count(*) as tcnt from exam_record r where r.exam_id in (select * from unfi) 
    group by exam_id
)

select uf.exam_id, ucnt, round(ucnt/tcnt, 3) from unfi uf left join unficnt uc on uf.exam_id = uc.exam_id
left join total tt on tt.exam_id = uf.exam_id
group by uf.exam_id, ucnt
order by uf.exam_id

老实用公用表表达式得了