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

SELECT t1.difficult_level, ROUND(SUM(right_answer)/COUNT(t1.question_id),4) correct_rate
FROM
    (SELECT u.device_id, u.university, qp.question_id, difficult_level, result, 
 CASE WHEN result = 'right' THEN 1 ELSE 0 END AS right_answer
FROM question_practice_detail qp
 LEFT JOIN question_detail qd 
  ON qd.question_id = qp.question_id
 LEFT JOIN user_profile u 
  ON u.device_id = qp.device_id AND university = '浙江大学'
    ) t1
WHERE university = '浙江大学'
GROUP BY t1.difficult_level
ORDER BY correct_rate ASC

正确率主要结合SUM和COUNT函数得出