牛客网打卡:SQL35 浙大不同难度题目的正确率
不要写又臭又长的SQL语句
从刚开始理解
-
要统计正确率,所以正确的个数和错误的个数肯定是要统计的,正确的个数,那么通过sum和if函数进行统计
sum(if(result='right',1,0)) / count(result)
-
重要的是表的链接,第一张表示
question_practice_detail
或者question_detail
,得到question_id
和difficult_level
的关联
然后进行子查询
或者是进行连表
select
difficult_level,
sum(if(result='right',1,0)) / count(result) as corrent_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 u where u.university='浙江大学')
group by difficult_level
order by corrent_rate
select
difficult_level,
sum(if(result='right',1,0)) / count(result) as corrent_rate
from question_practice_detail q1 left join question_detail q2 on q1.question_id=q2.question_id
left join user_profile u on q1.device_id=u.device_id where u.university='浙江大学')
group by difficult_level
order by corrent_rate