SQL Server中的排名|极客教程

SQL Server中的排名

在SQL Server中，排名是一个常见的问题，特别是在需要按照某种顺序对数据进行排名并进行筛选或汇总的情况下。SQL Server提供了一些内置函数来帮助我们实现这些功能，比如RANK()、DENSE_RANK()、ROW_NUMBER()等。在本文中，我们将详细介绍这些常用的排名函数及其使用方法。

准备工作

在开始之前，我们先创建一个示例的表来演示排名函数的使用。假设我们有一个名为Students的表，结构如下：

CREATE TABLE Students (
    Id INT,
    Name VARCHAR(50),
    Score INT
);

INSERT INTO Students (Id, Name, Score)
VALUES (1, 'Alice', 80),
       (2, 'Bob', 90),
       (3, 'Charlie', 85),
       (4, 'David', 95),
       (5, 'Eve', 75);

RANK()

RANK()函数用于计算同一排序中值的排名，如果有相同的值，则会存在并列排名。语法如下：

RANK() OVER (PARTITION BY column1 ORDER BY column2)

其中：

column1是要分组进行排名的列
column2是用来进行排序的列

让我们看一个实际的示例，计算学生成绩的排名：

SELECT Id, Name, Score,
       RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

运行结果如下：

Id  | Name    | Score | Rank
-------------------------------
4   | David   | 95    | 1
2   | Bob     | 90    | 2
3   | Charlie | 85    | 3
1   | Alice   | 80    | 4
5   | Eve     | 75    | 5

在上面的示例中，我们按照学生成绩降序排名，并且不存在并列排名。

DENSE_RANK()

DENSE_RANK()函数与RANK()函数类似，但是不会存在并列排名，即排名是连续的。语法如下：

DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)

让我们使用DENSE_RANK()函数计算学生成绩的排名：

SELECT Id, Name, Score,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Students;

运行结果如下：

Id  | Name    | Score | DenseRank
------------------------------------
4   | David   | 95    | 1
2   | Bob     | 90    | 2
3   | Charlie | 85    | 3
1   | Alice   | 80    | 4
5   | Eve     | 75    | 5

在上面的示例中，我们使用DENSE_RANK()函数按照学生成绩降序排名，并且排名是连续的。

ROW_NUMBER()

ROW_NUMBER()函数用于为结果集中的每一行分配一个唯一的连续整数值。语法如下：

ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

接下来，让我们使用ROW_NUMBER()函数为学生成绩分配排名：

SELECT Id, Name, Score,
       ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNumber
FROM Students;

运行结果如下：

Id  | Name    | Score | RowNumber
----------------------------------
4   | David   | 95    | 1
2   | Bob     | 90    | 2
3   | Charlie | 85    | 3
1   | Alice   | 80    | 4
5   | Eve     | 75    | 5

与DENSE_RANK()函数不同，ROW_NUMBER()函数为每一行分配了唯一的整数值，不管值是否相同。

使用PARTITION BY进行分组

除了简单地按照某一列进行排名外，我们还可以使用PARTITION BY子句进行分组排名。这样可以在组内进行排名，而不是整体对数据进行排名。让我们看一个示例，按照学生成绩所在的班级进行排名：

SELECT Id, Name, Score,
       RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank
FROM Students;

在这个示例中，我们假设Students表中有一个Class列，表示学生所在的班级。我们使用PARTITION BY Class来实现在每个班级内对学生成绩进行排名。

综合示例

最后，让我们以一个综合示例来演示如何同时使用RANK()、DENSE_RANK()和ROW_NUMBER()函数，为学生成绩进行排名：

SELECT Id, Name, Score,
       RANK() OVER (ORDER BY Score DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank,
       ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNumber
FROM Students;

运行结果如下：

Id  | Name    | Score | Rank | DenseRank | RowNumber
-----------------------------------------------------
4   | David   | 95    | 1    | 1         | 1
2   | Bob     | 90    | 2    | 2         | 2
3   | Charlie | 85    | 3    | 3         | 3
1   | Alice   | 80    | 4    | 4         | 4
5   | Eve     | 75    | 5    | 5         | 5

在这个示例中，我们同时使用了RANK()、DENSE_RANK()和ROW_NUMBER()函数，演示了它们分别的排名效果。