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

SELECT 
    target.difficult_level,
    SUM(CASE WHEN target.result = 'right' THEN 1 ELSE 0 END) / COUNT(target.question_id) AS correct_rate
FROM (
    SELECT 
        qpd.question_id, 
        qpd.result,
        qd.difficult_level
    FROM question_practice_detail AS qpd
    LEFT JOIN user_profile AS up ON qpd.device_id = up.device_id
    LEFT JOIN question_detail AS qd ON qpd.question_id = qd.question_id 
    WHERE up.university = '浙江大学'
) AS target
GROUP BY target.difficult_level
ORDER BY correct_rate;

步骤拆解

① question_practice_detail 和 user_profile的左连接

② 对第①步得到的结果，对question_detail进行左连接

③ 对三张合并后的表，使用WHERE子句进行筛选，得到university = "浙江大学"的结果集，并命名为target

④ 对target按difficult_level进行分组(GROUP BY)

⑤ 在SELECT子句中使用条件子句，将正确的题目(right=1,wrong=0)数量进行累加，除以该难度类别下全部的题目(COUNT) 得到 correct_rate

⑥ 根据correct_rate，进行排序。