row_ number over函数的基本用法

 

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

函数作用：从1开始，为按组排序的每条记录添加一个序列号  函数只能用于select和order by子句中 不能用在where子句

 

不分组排序

不进行分组时语法为ROW_NUMBER() OVER(ORDER BY COLUMN)，如：

有一个表A就一个字段num，数据如下

num

10

20

30

查询语句为select row_number() over(order by num) as idx,num from A

结果如下

num      idx

10          1

20          2

30          3

 

分组排序

分组的话ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)表示根据COL1分组，在分组内部根据COL2排序，

而此函数的结果值就表示每组内部排序后的顺序编号（组内连续的惟一的）

表employee有数据如下

empid        deptid            salary

1               10                   5500.00

2               10                   4500.00

3                20                  1900.00

4               20                   4800.00

查询语句为：select *,row_number() over(partition by deptid 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

 

比较

可以看到这个函数不分组时的作用oracle自带row_num也能完成，差别就是row_num从0开始。分组排序这个功能就比较强大

另外还有两个类似函数rank() over() 和dense_rank() over()

区别就是如果排序字段有重复值

row_number()函数还是1 2 3排下去

rank() over()则会出现 1  1  3

dense_rank() over() 则会出现 1 1 2

这三种情况 就是给的序号不一样