Oracle 的 ROW_NUMBER() OVER()函数

语法

ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

数据

CREATE TABLE emp (empid NUMBER(9) ,deptid NUMBER(9) ,salary DECIMAL(10,2));

INSERT INTO EMP VALUES (1, 10, 5500.00);
INSERT INTO EMP VALUES (2, 10, 4500.00);
INSERT INTO EMP VALUES (3, 20, 1900.00);
INSERT INTO EMP VALUES (4, 20, 4800.00);
INSERT INTO EMP VALUES (5, 40, 6500.00);
INSERT INTO EMP VALUES (6, 40, 14500.00);
INSERT INTO EMP VALUES (7, 40, 44500.00);
INSERT INTO EMP VALUES (8, 50, 6500.00);
INSERT INTO EMP VALUES (9, 50, 7500.00);

实战

SELECT e.*,ROW_NUMBER() OVER(PARTITION BY deptid ORDER BY salary DESC ) RANK FROM emp e;

• 解释
  ROW_NUMBER() OVER(PARTITION BY deptid ORDER BY salary DESC )表示根据deptid分组,在分组内根据salary降序排序，计算的结果就是每一个组内记录的排名。因此就可以查询每个部门的工资等级情况。

• 查询结果