工作中遇到的一个问题,需要对某列进行分组排序,取其中排序的第一条数据项
用到了row_number() over(partition by col1 order by col2)来解决此问题。
实例准备:
create or replace table employee ( empid int, deptid int, salary decimal(10,2) ); insert into employee values(1,10,234.00); insert into employee values(2,10,1233.00); insert into employee values(3,20,600.00); insert into employee values(4,20,4512.00); insert into employee values(5,30,3424.00); insert into employee values(6,30,1232.00); insert into employee values(7,40,4445.00); insert into employee values(8,40,9999.00); insert into employee values(9,40,212000.00);
进行查询操作:
select deptid ,salary ,row_number() over(partition by deptid order by salary desc) as rn from employee;
执行结果:
从上面的结果可以看出来是对部门进行分组,按照薪水来进行排序,之后的处理可以根据自己的需求来进行处理就ok了!