oracle ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
程序员文章站
2022-07-05 17:27:24
工作中遇到的一个问题,需要对某列进行分组排序,取其中排序的第一条数据项 用到了ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)来解决此问题。 实例准备: 进行查询操作: 执行结果: 从上面的结果可以看出来是对部门进行分组,按照薪水来进行排序,之后的 ......
工作中遇到的一个问题,需要对某列进行分组排序,取其中排序的第一条数据项
用到了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了!
上一篇: 91. Decode Ways
下一篇: ES5和ES6新的操作数组的方法(常用)
推荐阅读
-
oracle 之分析函数 over (partition by ...order by ...)
-
oracle sum(col1) over(partition by col2 order by col3):实现分组递增汇总
-
oracle ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
-
MYSQL-实现sqlserver- row_number() over(partition by order by) 分组排序功能
-
MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序_MySQL
-
oracle 之分析函数 over (partition by ...order by ...)
-
row_number() over (partition by a.sql_id order by a.id desc ) r
-
Row_number() OVER(PARTITION BY xxx ORDER BY XXX)分组排序_MySQL
-
Row_number() OVER(PARTITION BY xxx ORDER BY XXX)分组排序_MySQL
-
MYSQL-实现sqlserver- row_number() over(partition by order by) 分组排序功能