1,只求浙江大学,difficult_level,result又在其它两表中,因此考虑用inner join连接过滤出三表共同字段的数据来看看

selectup.device_id,up.university,qpd.device_id,qpd.question_id,qpd.result,qd.question_id,qd.difficult

from user_profile up inner join question_practice_detail qpd

on up.device_id = qpd.device_id

inner join question_detail qd

on qpd.question_id = qd.question_id

where university = '浙江大学' alt 2,目前来看想要的数据都在这张表中了,接下来使用聚合函数整理一下数据

正确答题数/总答题数 = 正确答题率

sum(if(result='right',1,0))/ count(qpd.question_id) as correct_rate

3,最终代码

select qd.difficult_level,

sum(if(result='right',1,0))/ count(qpd.question_id) as correct_rate

from user_profile up inner join question_practice_detail qpd

on up.device_id = qpd.device_id

inner join question_detail qd on qpd.question_id = qd.question_id

where university = '浙江大学'

group by difficult_level

order by correct_rate