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

# 将两个表合并,计算出不同等级的答题正确率
select
    a.difficult_level,
    sum(a.right_result_cnt)/sum(a.question_cnt_1) as correct_rate
from
(
select   # 每个同学在不同难度下答题的数，答题正确数
    qpd.device_id,
    qd.difficult_level,
    count(qpd.question_id) as question_cnt_1,
    sum(if(qpd.result='right',1,0)) as right_result_cnt
from question_practice_detail as qpd
left join question_detail as qd
on qpd.question_id=qd.question_id
group by qpd.device_id,qd.difficult_level
) as a

left join user_profile as up
on a.device_id=up.device_id
where university='浙江大学'
group by a.difficult_level
order by correct_rate