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

select 
   difficult_level,
   sum(if(result = "right",1,0))/count(qpd.question_id) correct_rate
from user_profile up
left join question_practice_detail qpd
on up.device_id = qpd.device_id
left join question_detail qd
on qd.question_id = qpd.question_id
where university = "浙江大学"
group by difficult_level
having difficult_level != "None"
order by correct_rate asc

1. 因为条件限定需要是浙江大学的用户，所以需要是user_profile表的并且能统计出题目难度的记录，因此用user_profile表inner join另外两张表。
2. join方式选择：如果前面inner join改成left join，为了防止结果中有难度为None的结果，需要在order by前加一句 having qd.difficult_level != 'None'，不加实测会出现none