HDC·Cloud | GaussDB T 其他函数ROW_NUMBER() OVER
GaussDB_数据库2020-03-31 17:04:07

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.


0
0
写文章
戳我,来吐槽~