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

select d.difficult_level,round(sum(case when q.result='right' then 1 else 0 end) /count(difficult_level),4) as correct_rate from user_profile as u join question_practice_detail as q on u.device_id=q.device_id join question_detail as d on q.question_id = d.question_id where u.university="浙江大学" group by d.difficult_level order by correct_rate ASC;

这一题需要关联三张表，使用内连接分别关联user_profile、question_practice_detail、question_detail分别简称u表、q表、d表

第一步筛选出浙江大学所有答题记录包含question_id、difficult_level、ressult

select d.question_id,d.difficult_level,q.result from user_profile as u join question_practice_detail as q on u.device_id=q.device_id join question_detail as d on q.question_id = d.question_id where u.university="浙江大学";

第二步使用难度相同的正确的答题记录数量除以答题总数并保留四位小数:round(sum(case when q.result='right' then 1 else 0 end) / count(difficult_level),4)计算出答题正确率correct_rate

然后以题目难度分组

group by d.difficult_level

select d.difficult_level,round(sum(case when q.result='right' then 1 else 0 end) /count(difficult_level),4) as correct_rate from user_profile as u join question_practice_detail as q on u.device_id=q.device_id join question_detail as d on q.question_id = d.question_id where u.university="浙江大学" group by d.difficult_level;

最后一步按照正确率correct_rate升序排列，order by correct_rate ASC