Oracle之ROW_NUMBER() OVER (PARTITION BY COLUMN1 ORDER BY COLUMN2) 函数

1、ROW_NUMBER() OVER (PARTITION BY COLUMN1 ORDER BY COLUMN2) 函数

 表示根据COLUMN1 分组，在分组内部根据 COLUMN2排序，而此函数计算的值就表示每组内部排序后的顺序编号

（其值是组内连续的唯一的)

CREATE TABLE EMP (EMPID INT ,DEPTID INT ,SALARY DECIMAL(10,2),NAME VARCHAR(225));
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('1', '10', '5500', '张三');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('2', '10', '4500', '李四');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('3', '20', '1900', '王五');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('4', '20', '4800', '赵六');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('5', '40', '6500', '孙琦');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('6', '40', '14500', '冯八');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('7', '40', '44500', '钱九');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('8', '50', '6500', '周零');
INSERT INTO "EMP"("EMPID", "DEPTID", "SALARY", "NAME") VALUES ('9', '50', '7500', '郑bug');
SELECT * FROM EMP;

效果：

2、根据SALARY 升序排序 (当然这样的排序也可以直接在WHERE之后ORDER BY 实现)

SELECT E.*,ROW_NUMBER() OVER(ORDER BY SALARY) ROWNUMBER FROM EMP E;

效果：

3、根据DEPTID分组并在分组内部根据SALARY排序

SELECT E.*,ROW_NUMBER() OVER(PARTITION BY DEPTID ORDER BY SALARY) ROWNUMBER FROM EMP E;

效果：