题解 | #浙大不同难度题目的正确率#

select distinct
    difficult_level,
    sum(if (b.result = 'right', 1, 0)) / count(b.question_id) as correct_rate
from
    user_profile a
    left join question_practice_detail b on a.device_id = b.device_id
    left join question_detail c on b.question_id = c.question_id
where
    university = '浙江大学'
    and b.question_id is not null
group by
    c.difficult_level
order by
    correct_rate

联表查询筛选出浙江大学，特别注意的是连第三张表的时候需要排除题目id为空的情况，否则由于左联，最终的视图会带上一些question_id为空的记录导致统计出错，联表查询后按照难度分组，最后用正确的题目数除总的题目数即可得出正确率。