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

select
  difficult_level,
  round(
    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id),
    4
  ) as correct_rate
from
  user_profile as up
  inner join question_practice_detail as qpd on up.device_id = qpd.device_id
  inner join question_detail as qd on qd.question_id = qpd.question_id
  and up.university = '浙江大学'
group by
  difficult_level
order by
  correct_rate

取三张表的交集，所以可以用inner join来连接三张表，关键就是判断答题正确率的写法，正确率==正确题目个数/答题总个数，可以通过if判断，if(expr,value1, value2)