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

SELECT difficult_level,COUNT(IF(result='right',1,NULL))/COUNT(qpd.question_id) correct_rate
FROM  question_detail qd, question_practice_detail qpd,user_profile up
WHERE up.device_id=qpd.device_id and qpd.question_id=qd.question_id and
university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate;

好奇怪和上一题一样，COUNT(IF(result='right',1,0))不对。？
另外上面的这个写法简单粗暴地把三个表连接了起来。可以做得更精细一点，减少笛卡尔积的运算量。

SELECT difficult_level,COUNT(IF(result='right',1,NULL))/COUNT(qpd.question_id) correct_rate
FROM 
(SELECT device_id FROM user_profile WHERE university='浙江大学') a
INNER JOIN question_practice_detail qpd
ON a.device_id=qpd.device_id

INNER JOIN question_detail qd
ON qpd.question_id=qd.question_id
GROUP BY difficult_level
ORDER BY correct_rate;

mark一下，(SELECT device_id FROM user_profile WHERE university='浙江大学')要另外取个表名a，不然会报错。