sql中row-ag捕鱼王app官网

尽管这三个都是 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 的情况下，并列中的两行将具有相同的排名并且不会有差距。 下一个不同的等级将按顺序排列。