与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码。
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序)。
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 employee values(7,40,44500.00);
insert into employee values(8,40,55500.00);
insert into employee values(9,50,6500.00);
insert into employee values(10,50,7500.00);
commit;
SQL> SELECT T.*,ROW_NUMBER() OVER(PARTITION BY deptid ORDER BY deptid) FO
2 from employee t;
EMPID DEPTID SALARY FO
---------- ---------- ---------- ----------
1 10 5500 1
2 10 4500 2
3 20 1900 1
4 20 4800 2
5 40 6500 1
8 40 55500 2
6 40 14500 3
7 40 44500 4
9 50 6500 1
10 50 7500 2