本文已参与「新人创作礼」活动，一起开启掘金创作之路。
🔖文章摘要
题目选自牛客网在线编程SQL实战，文章包含一组题，题目难度依次加深，内容依次为：①题目介绍、②代码编写要求、③解题思路、④解题代码和⑤要点总结共五大部分。
📝题目一（难度：简单）
📌题目介绍
牛客每次考试完，都会有一个成绩表(grade)，如下:（解释：第1行表示用户id为1的用户选择了C++岗位并且考了11001分......第8行表示用户id为8的用户选择了JS岗位并且考了9999分）
📌编写要求
请写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):
📌解题思路
📌解题代码
select job,round(avg(score),3)
from grade
group by job
order by score desc;
📌要点总结
- 此题较为简单，主要考查聚合函数的使用以及排序
📝题目二（难度：中等）
📌题目介绍
牛客每次考试完，都会有一个成绩表(grade)，如下:（解释：第1行表示用户id为1的用户选择了C++岗位并且考了11001分......第8行表示用户id为8的用户选择了前端岗位并且考了9999分)
📌编写要求
请写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:
📌解题思路
📌解题代码
select grade.*
from grade
left join (
select job,avg(score) as ag
from grade
group by job
) as agt on grade.job=agt.job
where score>agt.ag
order by id;
📌要点总结
- 该题在第一道题的基础上多考查了多表连接的使用
📝题目三（难度：中等）
📌题目介绍
牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表和不同的语言岗位(language)表简化如下:（解释：第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000......第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000)
📌编写要求
请找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:
📌解题思路
📌解题代码
select table1.id,name,score
from (
select *,
dense_rank() over(partition by language_id order by score desc) as rak
from grade
) as table1
left join language on language.id=table1.language_id
where rak=1 or rak=2
order by name,score desc,table1.id;
📌要点总结
-
这道题主要考查分组+排序，这种情况下使用dense_rank()over(partition by字段 order by 字段)比较容易解决，此外rank()也可以代替dense_rank()，只不过在这道题中使用起来不是很方便
-
此题的另一解法：不使用排序函数，使用select自联表格本身来获取当前分数在当前岗位的排名
select g1.id, l.name, g1.score from grade as g1 join language as l on g1.language_id=l.id where ( select count(distinct g2.score) from grade as g2 where g2.score>=g1.score and g1.language_id=g2.language_id ) <=2 order by l.name,g1.score desc ,g1.id;
📝题目四（难度：较难）
📌题目介绍
牛客每次考试完，都会有一个成绩表(grade)，如下:（解释：第1行表示用户id为1的用户选择了C++岗位并且考了11001分......第8行表示用户id为8的用户选择了B语言岗位并且考了9999分）
📌编写要求
请写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:
（解释：第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)
第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)
第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为B语言岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)
📌解题思路
📌解题代码
select job,
floor((count(*)+1)/2 ) as "start",
ceil((count(*)+1)/2) as "end"
from grade
group by job
order by job;
📌要点总结
-
这道题实际上解题思路是比较简单的，但容易被编写要求误导，导致想得很复杂，因此不要一味地依照编写要求去思考解题方案
-
floor()是向下取整函数，ceil()是向上取整函数
-
该题的另一解法：向下取整函数floor()和向上取整函数ceil()的作用实际上可以用round()四舍五入函数替代！
select grade.job, round(count(grade.id)/2), round((count(grade.id)+1)/2) from grade group by grade.job order by job
📝题目五（难度：困难）
📌题目介绍
牛客每次考试完，都会有一个成绩表(grade)，如下:（解释：第1行表示用户id为1的用户选择了C++岗位并且考了11001分......第8行表示用户id为8的用户选择了B语言岗位并且考了9999分）
📌编写要求
请写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:
（解释：第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2
第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1
第4行表示B语言岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2）
📌解题思路
📌解题代码
select id,rak.job,score,t_rank
from (
select *,row_number()over(partition by grade.job order by score desc) as t_rank
from grade) as rak
left join (
select job,ceil((count(*)+1)/2) as 'start',floor((count(*)+1)/2) as 'end'
from grade
group by job
) as mid on rak.job=mid.job
where t_rank=start or t_rank=end
order by id;
📌要点总结
-
这道题在上一道题的解题铺垫下难度大大降低，主要考查row_number()函数的巧妙使用
-
该题的另一解法：无论奇偶，中位数的位置距离（个数+1）/2小于1， 由上一道题的解题思路里的表格可以验证，解题代码如下
select id,job,score,s_rank from (select * ,(row_number()over(partition by job order by score desc))as s_rank ,(count(score)over(partition by job))as num from grade) as table1 where abs(table1.s_rank-(table1.num+1)/2)<1 order by id;
