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

新手不带IF版本的题解
话说这题好坑，查了好久才发现这question_practice_detail表中的id竟然不是主键还带重复...

SELECT 
    res.difficult_level, 
    round(count(qpd.result) / count(res.result), 4) AS correct_rate
FROM
    (SELECT
        qd.difficult_level,
        qpd.result,
        qpd.id,
        qpd.device_id,
        qpd.question_id
    FROM question_practice_detail AS qpd
    INNER JOIN user_profile AS up
    ON qpd.device_id = up.device_id
    AND up.university = '浙江大学'
    INNER JOIN question_detail AS qd
    ON qpd.question_id = qd.question_id)
    AS res
LEFT JOIN question_practice_detail AS qpd
ON res.id = qpd.id
AND res.device_id = qpd.device_id
AND res.question_id = qpd.question_id
AND qpd.result = 'right'
GROUP BY res.difficult_level
ORDER BY correct_rate

不用IF的版本