有些时候我们希望得到指定数据中的前n列,示例如下:
得到每个部门薪水最高的三个雇员:
先创建示例表
createtableemp
as
select*fromscott.emp;
altertableemp
addconstraintemp_pk
primarykey(empno);
createtabledept
as
select*fromscott.dept;
altertabledept
addconstraintdept_pk
primarykey(deptno);
先看一下row_number() /rank()/dense_rank()三个函数之间的区别
selectemp.deptno,emp.sal,emp.empno,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
结果如下:
105000.007839111
102450.007782222
101300.007934333
203000.007788111
203000.007902211
202975.007566332
201100.007876443
20800.007369554
302850.007698111
301600.007499222
取每个部门的薪水前三位雇员:
selectt.deptno,t.rank,t.salfrom
(
selectemp.*,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
)t
wheret.rank<=3
结果如下:
1015000.00
1022450.00
1031300.00
2013000.00
2013000.00
2032975.00
3012850.00
3021600.00
3031500.00
如果想输出成deptno sal1 sal2 sal3这种类型的格式
步骤一(decode):
selectt.deptno,decode(row_number,1,sal)sal1,decode(row_number,2,sal)sal2,decode(row_number,3,sal)sal3from
(
selectemp.*,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
)t
wheret.rank<=3
结果如下:
105000
10 2450
10 1300
203000
20 3000
20 2975
302850
30 1600
30 1500
步骤二(使用聚合函数去除null,得到最终结果):
selectt.deptno,max(decode(row_number,1,sal))sal1,max(decode(row_number,2,sal))sal2,max(decode(row_number,3,sal))sal3from
(
selectemp.*,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
)t
wheret.rank<=3
groupbyt.deptno
结果如下:
10500024501300
20300030002975
30285016001500
分享到:
相关推荐
MS SQL 2005 四个排序函数ROW_NUMBER、RANK、DENSE_RANK 和 NTILE简介用法结果排名排序
分析函数ROW_NUMBER、RANK、DENSE_RANK的用法
hive中分组取topN、row_number、rank和dense_rank使用介绍
排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数row_number、rank、dense_rank和ntile,需要的朋友可以参考下。
本文主要是对Oracle分析函数Rank, Dense_rank, row_number的使用法,通过这些函数,我们可以对数据进行排序和分组,需要的朋友可以参考下。
排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3.窗口函数进阶-滚动统计(累积/均值) 4.窗口函数进阶-根据时间...
ROW_NUMBER()说明:返回结果集...参数:<partition> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 <order>:确定将 ROW_NUMBER 值分配给分区中的行的顺序。返回类型:bigint 。示例:/*以下示例将
HighRes-MVSNet_A_Fast_Multi-View_Stereo_Network_for_Dense_3D_Reconstruction_From_High-Resolution_Images--2021 HighRes-MVSNet_A_Fast_Multi-View_Stereo_Network_for_Dense_3D_Reconstruction_From_High-...
分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () OVER([partition_clause]...
“rank()、dense_rank()和row_number()的区别”文章的date
目录 Oracle开发专题之:分析函数(OVER) Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number) Oracle开发专题之:分析函数3(Top/Bottom N、First/Last、NTile) Oracle开发专题之:窗口函数 ...
主要介绍了sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题之:报表函数 6 Oracle开发专题之:...
Dense_RGB-D_SLAM_with_multiple_cameras[1].pdf
Oracle分析函数——函数列表 SUM :该函数计算组中表达式的累积和 MIN :在一个组中的数据窗口中查找表达式的最小值 MAX :在一个组中的数据窗口中查找表达式的最大值 AVG :用于计算一个组和数据窗口内表达式的...
oracle分析函数,窗口函数,报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)