迹忆客 专注技术分享

尽管这三个都是 SQL 中的排名函数，在 Microsoft SQL Server 中也称为 Window 函数，但是当在排名上有联系时，即重复记录，rank()、dense_rank() 和 row_number() 之间的区别就会出现。 例如，如果按薪水对员工进行排名，那么薪水相同的两名员工的排名是多少？ 这取决于使用的排名函数，例如 row_number、rank 或 dense_rank。

row_number() 函数总是生成一个唯一的排名，即使有重复的记录，即如果 ORDER BY 子句不能区分两行，它仍然会给他们不同的排名，尽管哪个记录会出现在前面或后面是随机决定的，就像我们的示例 2 员工 Shane 和 Rick 的薪水相同，并且排号为 4 和 5，这是随机的，如果我们再次运行，Shane 可能会排在第 5 位。

rank() 和 dense_rank() 将对无法通过 order by 子句区分的行给予相同的排名，但 dense_rank 将始终生成连续的排名序列，如 (1,2,3,...)，而 rank () 会在相同排名的两行或更多行后留下空缺（想想“奥运会”：如果两个运动员获得金牌，就没有第二名，只有第三名）。

令人惊讶的是，所有这些函数在 Microsoft SQL Server 和 Oracle 中的行为都相似，至少在高层是这样，所以如果你在 MSSQL 中使用过它们，你也可以在 Oracle 11g 或其他版本上使用它。

用于构建模式的 SQL

下面是用于创建表并向其中插入一些数据以用于演示目的的 SQL：

IF OBJECT_ID( 'tempdb..#Employee' ) IS NOT NULL DROP TABLE #Employee; 

CREATE TABLE #Employee (name varchar(10), salary int);

INSERT INTO #Employee VALUES ('Rick', 3000);
INSERT INTO #Employee VALUES ('John', 4000);
INSERT INTO #Employee VALUES ('Shane', 3000);
INSERT INTO #Employee VALUES ('Peter', 5000);
INSERT INTO #Employee VALUES ('Jackob', 7000);
INSERT INTO #Employee VALUES ('Sid', 1000);

你可以看到我们包括了两名薪水相同的员工，即 Shane 和 Rick，只是为了演示 SQL Server 中 row_number、rank 和 dense_rank 窗口函数之间的差异，当排名相同时这一点很明显。

ROW_NUMBER() 示例

它总是为每一行生成一个唯一的值，即使它们相同并且 ORDER BY 子句无法区分它们。 这就是为什么它被用来解决像我们之前看到的第二高薪水或第 n 高薪水这样的问题。

在下面的示例中，我们有两名薪水相同的员工，即使我们在薪水列上生成了行号，它也会为这两名薪水相同的员工生成不同的行号。

select e.*, row_number() over (order by salary desc) row_number from #Employee e 
result:

name salary row_number
Jackob 7000 1
Peter 5000 2
John 4000 3
Shane 3000 4
Rick 3000 5
Sid 1000 6

你可以在这个例子中看到，我们根据员工的薪水对员工进行排名，即使他们的薪水相同，他们每个人都有一个独特的排名。 Shane 和 Rick 的薪水相同，均为 3000，但他们分别排在第 4 和第 5 位。 值得注意的是，在平局的情况下，排名是随机分配的。

RANK() 示例

rank() 函数会将相同的排名分配给相同的值，即不能通过 ORDER BY 区分的值。 此外，下一个不同的等级不会从紧接的下一个数字开始，但会有一个差距，即如果第 4 名和第 5 名员工的薪水相同，那么他们将拥有相同的等级 4，而薪水不同的第 6 名员工将有一个新的 排名 6。

这是阐明这一点的示例：

select e.*, rank() over (order by salary desc) rank from #Employee e
result:

name salary rank 
Jackob 7000 1 
Peter 5000 2 
John 4000 3 
Shane 3000 4 
Rick 3000 4 
Sid 1000 6

你可以看到 Shane 和 Rick 都排在第 4 位，但是 Sid 排在第 6 位，而不是第 5 位，因为它保持了原来的顺序。

DENSE_RANK() 示例

dense_rank 函数类似于 rank() 窗口函数，即相同的值将被分配相同的等级，但下一个不同的值将具有比前一个等级高一个的等级，即如果第 4 名和第 5 名员工具有相同的等级 salary 那么他们将具有相同的等级，但是具有不同薪水的第 6 名员工将具有等级 5，这与 rank() 函数的等级 6 不同。 在 dense_rank() 的情况下，排名不会有差距，如下例所示：

select e.*, dense_rank() over (order by salary desc) dense_rank from #Employee e 
result:
name salary dense_rank 
Jackob 7000 1 
Peter 5000 2 
John 4000 3 
Shane 3000 4 
Rick 3000 4 
Sid 1000 5

我们可以看到 Shane 和 Rick 的排名相同，都是第 4 名，但 Sid 现在排名第 5 名，这与我们在前面的示例中使用 rank() 函数时的第 6 名不同。

row_number 与 rank 与 dense_rank 之间的区别

正如我所说，当存在重复记录时，rank、row_number 和 dense_rank 之间的差异是可见的。 因为在我们所有的例子中，我们都是根据薪水对记录进行排名，如果两条记录的薪水相同，那么我们会注意到这三个排名函数之间的区别。

row_number 给出了连续的数字，而 rank 和 dense_rank 给出了相同的重复排名，但是排名中的下一个数字是按照连续的顺序排列的，所以你会看到一个跳跃，但在 dense_rank 中排名没有任何差距。

select e.*,
row_number() over (order by salary desc) row_number,
rank() over (order by salary desc) rank,
dense_rank() over (order by salary desc) as dense_rank
from #Employee e

这里的输出清楚地显示了 rank() 和 dense_rank() 函数生成的排名差异。 这将消除大家对 rank、desnse_rank 和 row_nubmer 函数的疑虑。

我们可以看到员工 Shane 和 Rick 的薪水相同，均为 3000，因此当我们使用 rank() 和 dense_rank() 时，他们的排名相同，但下一个排名是 6，这是根据使用 rank() 的连续排名和 5 当我们 使用 dense_rank()。 row_number() 不会打破联系，并且始终为每条记录提供唯一编号。

顺便说一句，我在 Oracle 11g R2 和 Oracle 12c 上运行了所有三个 SQL 查询，结果相同。 因此，似乎 Oracle 和 SQL Server 都支持这些功能，并且它们的行为相同。

这就是 SQL SERVER 中 ROW_NUMBER()、RANK() 和 DENSE_RANK() 函数之间的区别。 正如我所说，差异归结为关系发生的时间。 在平局的情况下，ROW_NUMBER() 会给出唯一的行号，rank 会给出相同的排名，但是下一个不同的rank不会按顺序，会有差距。

在 dense_rank 的情况下，并列中的两行将具有相同的排名并且不会有差距。 下一个不同的等级将按顺序排列。