每日一题-3(分数排名)

题3:

根据下面给定的 Scores 表,编写一个 SQL 查询来实现分数排名。
要求:如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。

每日一题-3(分数排名)
(1)方法一:采用窗口函数
当涉及到排名可以使用窗口函数,首先需要知道窗口函数rank, dense_rank, row_number有什么区别,再根据题目的要求选择对应的函数。

  • rank():排名为相同时记为同一个排名,并且参与总排序,
  • dense_rank()排名相同时记为同一个排名,并且不参与总排序
  • row_number()排名相同时记为下一个排名不考虑并列名次的情况

题目要求是如果两个分数相同,则两个分数排名(Rank)相同并且排名需要连续,因此选择dense_rank()
代码如下:

SELECT Score, 
    DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores

(2)方法二:直接思维
当无法使用窗口函数时,就只有直接查询了。根据题目要求可以分为两个部分,第一部分是降序排列的分数,第二部分是每个分数对应的排名。

  • 第一部分代码:
select a.Score as Score
from Scores a
order by a.Score DESC
  • 第二部分可以先提取出大于等于X的所有分数集合R,将R去重后的元素个数就是X的排名。比如你考了98分,但最高的就有99分,那么去重之后集合R里就只有99和98两个元素,个数为2,因此你的Rank为2,代码如下:
select count(distinct b.Score) 
from Scores b 
where b.Score >= X as 'Rank';

最后直接把第一部分和第二部分的代码整合一下即可,第二部分的X就是第一部分的a.Score,代码如下:

select a.Score as Score,
   (select count(distinct b.Score) 
   from Scores b 
   where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC
上一篇:1012 The Best Rank (25 分)(排序)


下一篇:17 窗口函数