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 = '浙江大学' 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