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

WITH x AS (
  SELECT
    qpd.device_id AS device_id,
    qpd.question_id AS question_id,
    qpd.result AS result,
    u.university AS university 
  FROM
    question_practice_detail qpd
    LEFT JOIN user_profile u ON u.device_id = qpd.device_id 
  WHERE
u.university = '浙江大学') SELECT
qd.difficult_level AS difficult_level,
sum(IF(x.result = 'right', 1, 0)) / sum(IF(x.result IN ('right', 'wrong'), 1, 0)) AS correct_rate 
FROM
  x
  LEFT JOIN question_detail qd ON x.question_id = qd.question_id 
GROUP BY
  difficult_level 
ORDER BY
  correct_rate

嵌套了一下外连接，内存占用比较多但是可以输出