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

结果

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

过程

一步一步来，提取关键字 浙大用户 不同难度 正确率，然后给了三个表 user_profile question_practice_detail question_detail，第一个想法是 把表的数量减少 步骤如下

(1)将question_practice_detail和question_detail进行连接，计算正确率

(2) 在where中进行子查询，选出属于浙江大学的device_id

(3)按difficult_level分组 correct_raet升序排序

简化

select 
    q2.difficult_level,
    round(avg(if(q1.result='right',1,0)),4) as correct_rate
from 
    question_practice_detail q1 left join question_detail q2
on q1.question_id = q2.question_id
where q1.device_id in (select device_id from user_profile where university='浙江大学' )
group by q2.difficult_level
order by correct_rate