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

/*select difficult_level,sum(if(result='wrong',0,1))/count(qpd.question_id)
from user_profile u
left outer join question_practice_detail qpd
on u.device_id = qpd.device_id
left outer join question_detail q
on qpd.question_id = q.question_id
where university = '浙江大学'
group by difficult_level*/
#上面的做法是错误的，一开始u表和qpd不应该是u表左连接qpd表，因为我们要统计的数据，其数量来源实质上来源于qpd表，u表和q表分别起到补充“大学 ”信息以及“难度”信息而已。如果链接错误，则在后面count的时候会出现none值。

select difficult_level ,sum(if(result='right',1,0))/count(qpd.result) as correct_rate
from user_profile u
right outer join question_practice_detail qpd
on u.device_id = qpd.device_id
left outer join question_detail q
on qpd.question_id = q.question_id
where university = '浙江大学'
group by difficult_level
order by correct_rate