SqlServer ROW_NUMBER() OVER 函数

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

简单的说row_number()从1开始，为每一条分组记录返回一个数字;
例一：
ROW_NUMBER() OVER (ORDER BY cs1 DESC)
是先把cs1列降序，再为降序以后的每条cs1记录返回一个序号

例二：
ROW_NUMBER() OVER (PARTITION BY cs1 ORDER BY cs2 DESC)
表示根据cs1分组，在分组内部根据 cs2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

实例：
创建表结构：
create table employee (empid int ,deptid int ,salary decimal(10,2))

添加表数据：
insert into employee values(1,10,5500.00)insert into employee values(2,10,4500.00)insert into employee values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into employee values(5,40,6500.00)insert into employee values(6,40,14500.00)insert into values(7,40,44500.00)insert into employee values(8,50,6500.00)insert into employee values(9,50,7500.00)

返回如下结果

+------------+----------+----+
| empid| depid | Salary |
+------------+----------+----+
| 1    | Max   | 5500.00 |
| 2    | Jim   | 4500.00 |
| 3    | Henry | 1900.00 |
| 4    | Max   | 4800.00 |
| 5    | Jim   | 6500.00 |
| 6    | Henry | 14500.00|
| 7    | Max   | 44500.00|
| 8    | Jim   | 6500.00 |
| 9    | Henry | 7500.00 |
+------------+----------+----+

需求一：按工资降序

预期结果：

+------+-------+----------+------+-----+
| empid| deptid |  Salary |  rank |
+------+-------+----------+------+-----+
| 7    |   40  | 5500.00 |   1   |
| 6    |   40  | 4500.00 |   2   |
| 9    |   50  | 1900.00 |   3   |
| 8    |   50  | 4800.00 |   4   |
| 5    |   40  | 6500.00 |   5   |
| 1    |   10  | 14500.00|   6   |
| 4    |   20  | 44500.00|   7   |
| 2    |   10  | 6500.00 |   8   |
| 3    |   20  | 7500.00 |   9   |
+------+-------+----------+------+-----+

Sql语句：

select *,ROW_NUMBER() OVER(ORDER BY salary desc) rank from employee

需求二：按部门分组，再按工资降序

预期结果：

+------+-------+----------+------+-----+
| empid| deptid |  Salary |  rank |
+------+-------+----------+------+-----+
| 1    |   10  | 5500.00 |   1   |
| 2    |   10  | 4500.00 |   2   |
| 4    |   20  | 4800.00 |   1   |
| 3    |   20  | 1900.00 |   2   |
| 7    |   40  | 44500.00|   1   |
| 6    |   40  | 14500.00|   2   |
| 5    |   40  | 6500.00 |   3   |
| 9    |   50  | 7500.00 |   1   |
| 8    |   50  | 6500.00 |   2   |
+------+-------+----------+------+-----+

Sql语句：

select *,ROW_NUMBER() OVER(PARTITION BY deptid  ORDER BY salary desc) rank from employee