题解 | #浙大不同难度题目的正确率#
https://www.nowcoder.com/practice/d8a4f7b1ded04948b5435a45f03ead8c
SELECT difficult_level, sum(if(t1.result='right',1,0))/count(*) correct_rate
FROM (SELECT d.difficult_level, p.device_id , p.result
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id) t1
left join user_profile u on u.device_id=t1.device_id
WHERE u.university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;
FROM (SELECT d.difficult_level, p.device_id , p.result
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id) t1
left join user_profile u on u.device_id=t1.device_id
WHERE u.university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;
1. 子查询
SELECT d.question_id, d.difficult_level, p.device_id , p.result
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id
先将question_detail表和question_practice_detail表通过question_id关联起来，将difficult_level，device_id，result查询出来，device_id用于关联user_profile 表，result用于统计正确率
2. 关联user_profile
SELECT 这里要查的就是最后的结果了，difficult_level，以及正确率
FROM （
SELECT d.question_id, d.difficult_level, p.device_id , p.result
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id
）t1 LEFT JOIN user_profile u ON t1.device_id=u.device_id
正确率：
想到的是right / count
right显然用sum求，可以用if来判断 sum(if (t1.result='right',1,0)) 这样就算出了正确的个数， count(*) 计算总个数， 当然使用这些函数需要进行GROUP BY
显然我们要按照难易程度进行GROUP BY
在GROUP BY 之前不要忘记，我们只需要查浙大的学生，所以 WHERE u.university='浙江大学'
3. 最后结果：
SELECT difficult_level, sum(if(t1.result='right',1,0))/count(*) correct_rate
SELECT difficult_level, sum(if(t1.result='right',1,0))/count(*) correct_rate
FROM (SELECT d.difficult_level, p.device_id , p.result
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id) t1
left join user_profile u on u.device_id=t1.device_id
WHERE u.university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;
SELECT difficult_level, sum(if(t1.result='right',1,0))/count(*) correct_rate
FROM (SELECT d.difficult_level, p.device_id , p.result
FROM question_detail d left join question_practice_detail p on d.question_id=p.question_id) t1
left join user_profile u on u.device_id=t1.device_id
WHERE u.university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;
腾讯成长空间 449人发布