函数语法: 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
这三种情况 就是给的序号不一样