SQL刷题记录四

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

链接：浙大不同难度题目的正确率_牛客题霸_牛客网 (nowcoder.com)

解题思路： 

• 表头重命名：根据输出示例，正确率用as语法重命名
• 升序输出：order by xxx asc
• 正确率的计算方式：判断result是否为right，是的话赋值为1，对于正确的数目，可以用count，也可以用sum，正确率还可以直接用avg计算。
• join方式选择：如果前面inner join改成left join，为了防止结果中有难度为None的结果，需要在order by前加一句 having qd.difficult_level != 'None'

select  distinct qd.difficult_level, 
        #三种不同的表示语句
        avg(if(qpd.result='right', 1, 0)) as correct_rate
#       sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#       count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up
inner join question_practice_detail as qpd on up.device_id = qpd.device_id 
inner join question_detail as qd on qpd.question_id = qd.question_id 
where university = '浙江大学'
group by difficult_level
order by correct_rate asc;

题目10：SQL38 查找后降序排列

链接：查找后降序排列_牛客题霸_牛客网 (nowcoder.com)

解题思路： 先按照gpa降序，再按照age降序

SELECT device_id, gpa, age FROM user_profile ORDER BY gpa DESC, age DESC;

题目11：SQL39 21年8月份练题总数

链接：​​​​​​​21年8月份练题总数_牛客题霸_牛客网 (nowcoder.com)

解题思路： 使用year ；month ； count三个函数

select  count(distinct device_id) as did_cnt ,
        count(question_id) as question_cnt
from question_practice_detail
where year(date) = 2021 and month(date) = 8;