【Oracle之ROW_NUMBER（）函数】

Oracle之ROW_NUMBER（）函数

语法：

row_number()  over(partition  by  col_n[,col_m ...]  order  by  col_1[,col_2 ...])

示例：

--初始化数据

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,50,6500.00)
insert into employee values(9,50,7500.00)


--数据显示为

empid       deptid      salary
----------- ----------- ---------------------------------------
1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00
————————————————

-- 需求1：根据部门分组，显示每个部门的工资等级

-- 预期结果：

empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                                1
6           40          14500.00                                2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2
————————————————————————————————————————————————————————————————————————————————————

-- SQL脚本：

SELECT t.*, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank 

FROM employee t;


-- 注意:row_number()是非确定性的（如返回值为12,13,14,15.但结果又可能是12,14,13,15）


-- 需求2：根据部门分组，显示每个部门的工资最高的雇员信息
select r.* from (
	select t.*, row_number() over(partition by deptid order by salary desc) rank
	from employee t
) r
where r.rank = 1