牛客刷SQL题Day4

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

select difficult_level,avg(if(qpd.result='right',1,0)) correct_rate

from 
user_profile up
left join
question_practice_detail qpd
on up.device_id= qpd.device_id

left join 
question_detail qd
on qpd.question_id = qd.question_id 
where  university='浙江大学' 
group by qd.difficult_level
having qd.difficult_level!='None'
order by  correct_rate

踩坑一：

        多个表连接的方法：From之后直接连着join。

踩坑二：

        avg(if(qpd.result='right',1,0)) correct_rate 的用法。

踩坑三(最关键的踩坑,  很隐秘):

        下图difficult_level字段数据中没有None,但是代码中出现了None。这个问题的出现是由于连接的时候，有difficult_level字段中的数据出现了None，这个时候解决方案：

select difficult_level,avg(if(qpd.result='right',1,0)) correct_rate

from 
user_profile up
left join
question_practice_detail qpd
on up.device_id= qpd.device_id

left join 
question_detail qd
on qpd.question_id = qd.question_id 
where  university='浙江大学' 
group by qd.difficult_level
# having qd.difficult_level!='None'
order by  correct_rate

 SQL38 查找后降序排列

select device_id, gpa,age
from user_profile
order by  gpa desc ,age desc

       踩坑：

                降序，每个字段都要加desc