SQL Server row_number()

row_number() over (order by clause)

在 SQL SERVER 的 row_number() over() 中使用 order by 对 select 查询的结果进行排序，并为每一行赋予一个连续的编号。

对于数据：

id|user_id|name   |
--|-------|-------|
 1|      1|用户1的请假1|
 2|      2|用户2的请假1|
 3|      3|用户3的请假1|
 4|      2|用户2的请假2|
 5|      2|用户2的请假3|
 6|      1|用户1的请假2|

使用：

select ROW_NUMBER() over(order by user_id) rowNum, id, user_id, name from leave

查询结果为：

rowNum|id|user_id|name   |
------|--|-------|-------|
     1| 1|      1|用户1的请假1|
     2| 6|      1|用户1的请假2|
     3| 2|      2|用户2的请假1|
     4| 4|      2|用户2的请假2|
     5| 5|      2|用户2的请假3|
     6| 3|      3|用户3的请假1|

这里使用 rowNum 是一个从 1 开始连续递增的顺序编号。所以可以用来分页。

row_number() over (partition by clause order by clause)

使用 partition by order by 对 select 查询的结果进行分组排序，并为每个分组里面行赋予一个连续的编号。

对于上面的数据，使用

select ROW_NUMBER() over(partition by user_id order by user_id) rowNum, id, user_id, name from leave

查询结果为：

rowNum|id|user_id|name   |
------|--|-------|-------|
     1| 1|      1|用户1的请假1|
     2| 6|      1|用户1的请假2|
     1| 2|      2|用户2的请假1|
     2| 4|      2|用户2的请假2|
     3| 5|      2|用户2的请假3|
     1| 3|      3|用户3的请假1|

这里 rowNum 则表示的是每个分组内部从 1 开始连续递增的顺序编号。所以它可以用来在分组后找最新一条记录。

如先根据 user_id 分组，然后在分组中根据 id 降序排序，这样每个分组中的第一条数据都是最新的记录。

select rowNum, id, user_id, name from (select ROW_NUMBER() over(partition by user_id order by user_id, id desc) rowNum, id, user_id, name from leave ) l where l.rowNum = 1

查询结果为：

rowNum|id|user_id|name   |
------|--|-------|-------|
     1| 6|      1|用户1的请假2|
     1| 5|      2|用户2的请假3|
     1| 3|      3|用户3的请假1|

partition by 和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。

over 里的分组及排序的执行要晚于 where、group by、order by 的执行。