牛客网-SQL篇-全部试题-较难-SQL74 考试分数(三)

SQL

问题

每个岗位分数排名前2名的用户

解题

1.看到排名，想到窗口函数：rank()、dense_rank()、row_number()。本题中可能出现分数相同的情况，此时排名也相同，因此选用dense_rank()函数（rank()函数排列序号不连续、row_number()函数不考虑分数相同时排名并列情况）。先查看排序后的临时表，SQL代码如下：

select 
	g.id, 
	l.name, 
	g.score, dense_rank() over(partition by l.name order by g.score desc) r
from grade g join language l on g.language_id = l.id

临时表如下：

当语言（name）为JAVA时，出现并列排名（score=11000）。

2.此时只需要搜索排名前2的名单，因此需要再嵌套一层查询，只需 r<=2 即可。

select t.id, t.name, t.score from
	(select 
		g.id, 
		l.name, 
		g.score, dense_rank() over(partition by l.name order by g.score desc) r
	from grade g join language l on g.language_id = l.id) t
where r<= 2

注意： 在使用嵌套时，需要对被嵌套的部分重命名。此处将被嵌套的部分命名为t 。

3.根据限制条件，对结果进行排序，最终SQL代码如下：

select t.id, t.name, t.score from
	(select 
	g.id, 
	l.name, 
	g.score, 
	dense_rank() over(partition by l.name order by g.score desc) r
	from grade g join language l on g.language_id = l.id) t
where r<= 2
order by t.name, t.score desc, t.id

查询结果如下：

总结

1.根据问题的不同，对窗口函数的有选择的使用。
2.对被嵌套的查询重命名（本解题重命名为t）。
3.外层查询时，不是对原有表进行查询，是对临时表进行操作；因此在查询时，使用的表是临时表（本解题中，临时表命名为t ，此时最外层使用t.id，而非g.id）。