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

·连接查询 ·group by

首先将三张表连接后查询，同时直接限定仅选择'浙江大学'

SELECT up.device_id,university,qpd.question_id,qd.difficult_level as difficult_level,qpd.result as result from user_profile as up left join question_practice_detail as qpd on qpd.device_id=up.device_id left join question_detail as qd on qd.question_id=qpd.question_id where university='浙江大学' 连接查询得到的结果如下：

从查询得到的结果就可以很明显的看出，需要根据difficult_level进行分组，所以correct_rate为正确数sum(if(result='right',1,0))，除以 总答题量count(difficult_level)

整体的代码如下：

select difficult_level,as correct_rate from

(SELECT up.device_id,university,qpd.question_id,qd.difficult_level as difficult_level,qpd.result as result from user_profile as up left join question_practice_detail as qpd on qpd.device_id=up.device_id left join question_detail as qd on qd.question_id=qpd.question_id where university='浙江大学') as new_table

group by difficult_level having new_table.difficult_level != 'None' order by correct_rate