题解 | #浙大不同难度题目的正确率#
https://www.nowcoder.com/practice/d8a4f7b1ded04948b5435a45f03ead8c
私以为此题考点主要在于如何理解SQL的执行顺序：FROM，JOIN，WHERE，GROUP BY，HAVING，聚合函数，SELECT，ORDER BY的按先后顺序执行。
1、完整SQL如下。
SELECT
qd.difficult_level,
SUM(IF(qpd.result='right',1,0))/COUNT(qpd.id) AS correct_rate
FROM question_practice_detail qpd
LEFT JOIN question_detail qd
ON qpd.question_id = qd.question_id
WHERE qpd.device_id IN( SELECT
up.device_id
FROM user_profile up
WHERE up.university = '浙江大学')
GROUP BY qd.difficult_level
ORDER BY correct_rate;
2、通过问题编号关联答题记录和问题表，找出问题的难度等级
SELECT * FROM question_practice_detail qpd LEFT JOIN question_detail qd ON qpd.question_id = qd.question_id;
3、通过设备号筛选出“浙江大学”的答题详情
SELECT * FROM question_practice_detail qpd LEFT JOIN question_detail qd ON qpd.question_id = qd.question_id WHERE qpd.device_id IN( SELECT up.device_id FROM user_profile up WHERE up.university = '浙江大学');
4、根据问题难度聚合数据，并根据聚合数据计算结果。同时，注意下排序问题。
SELECT qd.difficult_level, SUM(IF(qpd.result='right',1,0))/COUNT(qpd.id) AS correct_rate FROM question_practice_detail qpd LEFT JOIN question_detail qd ON qpd.question_id = qd.question_id WHERE qpd.device_id IN( SELECT up.device_id FROM user_profile up WHERE up.university = '浙江大学') GROUP BY qd.difficult_level ORDER BY correct_rate;