ROW_NUMBER() OVER

语法：

ROW_NUMBER() OVER (partition by expr order by expr)

功能：只能用于column list中，对查询返回的数据先进行分组再进行排序，然后分组打上排序序号。

示例：

根据部门分组，显示每个部门的工资等级。

--删除表employee。
DROP TABLE IF EXISTS employee;
--创建表employee。
CREATE TABLE employee (staff_id INT,section_id INT,max_salary NUMBER(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);
--提交事务。
COMMIT;
--按部门查询员工工资，并进行排序。
SELECT *, Row_Number() OVER (partition by section_id ORDER BY max_salary desc) rank FROM employee;

STAFF_ID     SECTION_ID   MAX_SALARY                               RANK
------------ ------------ ---------------------------------------- ------------
1            10           5500                                     1
2            10           4500                                     2
4            20           4800                                     1
3            20           1900                                     2
7            40           44500                                    1
6            40           14500                                    2
5            40           6500                                     3

7 rows fetched.