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

# 第一步，先把需要的信息挑选后连接起来
select q1.device_id,q1.question_id,q2.difficult_level,result
from question_practice_detail as q1
join question_detail as q2
on q1.question_id = q2.question_id
where q1.device_id in
(select device_id from user_profile where university = '浙江大学')


# 第二步，求正确率，用sum(if(result = 'right',1,0))算正确题数
select difficult_level,
       sum(if(result = 'right',1,0))/count(1) as correct_rate
from
(
select q1.device_id,q1.question_id,q2.difficult_level,result
from question_practice_detail as q1
join question_detail as q2
on q1.question_id = q2.question_id
where q1.device_id in
(select device_id from user_profile where university = '浙江大学')
) as a
group by difficult_level
order by correct_rate asc