row_number() over()分组排序详解

最新推荐文章于 2022-12-01 16:24:14 发布
最新推荐文章于 2022-12-01 16:24:14 发布
阅读量731 收藏
点赞数
分类专栏： SQL语句 文章标签： row_number() over() 分组排序
原文链接：https://blog.csdn.net/qq_25221835/article/details/82762416
版权

row_number() over()分组排序功能：

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。

例一：

表数据：


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     create 
     
     table TEST_ROW_NUMBER_OVER(
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
           
     
     id 
     
     varchar(
     
     10) 
     
     not 
     
     null,
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
           
     
     name 
     
     varchar(
     
     10) 
     
     null,
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
            age 
     
     varchar(
     
     10) 
     
     null,
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
            salary 
     
     int 
     
     null
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     );
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select * 
     
     from TEST_ROW_NUMBER_OVER t;
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
    
    
    
   
   
  9. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     1,
     
     'a',
     
     10,
     
     8000);
    
    
    
   
   
  10. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     1,
     
     'a2',
     
     11,
     
     6500);
    
    
    
   
   
  11. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     2,
     
     'b',
     
     12,
     
     13000);
    
    
    
   
   
  12. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     2,
     
     'b2',
     
     13,
     
     4500);
    
    
    
   
   
  13. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     3,
     
     'c',
     
     14,
     
     3000);
    
    
    
   
   
  14. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     3,
     
     'c2',
     
     15,
     
     20000);
    
    
    
   
   
  15. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     4,
     
     'd',
     
     16,
     
     30000);
    
    
    
   
   
  16. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     insert 
     
     into TEST_ROW_NUMBER_OVER(
     
     id,
     
     name,age,salary) 
     
     values(
     
     5,
     
     'd2',
     
     17,
     
     1800);

一次排序：对查询结果进行排序（无分组）


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     order 
     
     by salary 
     
     desc) rn
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t

结果：

进一步排序：根据id分组排序


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     partition 
     
     by 
     
     id 
     
     order 
     
     by salary 
     
     desc) 
     
     rank
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t

结果：

 再一次排序：找出每一组中序号为一的数据


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select * 
     
     from(
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     partition 
     
     by 
     
     id 
     
     order 
     
     by salary 
     
     desc) 
     
     rank
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t)
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     where 
     
     rank <
     
     2

结果：

排序找出年龄在13岁到16岁数据，按salary排序


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     id,
     
     name,age,salary,row_number()
     
     over(
     
     order 
     
     by salary 
     
     desc)  
     
     rank
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     from TEST_ROW_NUMBER_OVER t 
     
     where age 
     
     between 
     
     '13' 
     
     and 
     
     '16'

结果：结果中 rank 的序号，其实就表明了 over(order by salary desc) 是在where age between and 后执行的

例二：

1.使用row_number（）函数进行编号，如

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

原理：先按psd进行排序，排序完后，给每条数据进行编号。

2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3.统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了：


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by totalPrice)
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
     
     as 
     
     rows,customerID,totalPrice, DID 
     
     from OP_Order

4.统计每一个客户最近下的订单是第几次下的订单：


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     with tabs as  
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     (  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by totalPrice)
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
     
     as 
     
     rows,customerID,totalPrice, DID 
     
     from OP_Order  
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
      )  
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     select 
     
     MAX(
     
     rows) 
     
     as 
     
     '下单次数',customerID 
     
     from tabs 
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     group 
     
     by customerID

5.统计每一个客户所有的订单中购买的金额最小，而且并统计改订单中，客户是第几次购买的：

思路：利用临时表来执行这一操作。

1.先按客户进行分组，然后按客户的下单的时间进行排序，并进行编号。

2.然后利用子查询查找出每一个客户购买时的最小价格。

3.根据查找出每一个客户的最小价格来查找相应的记录。


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         with tabs as  
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
          (  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by insDT) 
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     as 
     
     rows,customerID,totalPrice, DID 
     
     from OP_Order  
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         )  
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
         
     
     select * 
     
     from tabs  
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     where totalPrice 
     
     in   
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         (  
    
    
    
   
   
  9. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select 
     
     MIN(totalPrice)
     
     from tabs 
     
     group 
     
     by customerID  
    
    
    
   
   
  10. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
          )

6.筛选出客户第一次下的订单。

思路。利用rows=1来查询客户第一次下的订单记录。


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         with tabs as  
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         (  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by insDT) 
     
     as 
     
     rows,* 
     
     from OP_Order  
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         )  
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select * 
     
     from tabs 
     
     where 
     
     rows = 
     
     1 
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select * 
     
     from OP_Order

7.注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     select   
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         ROW_NUMBER() 
     
     over(
     
     partition 
     
     by customerID  
     
     order 
     
     by insDT) 
     
     as 
     
     rows,  
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         customerID,totalPrice, DID  
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
        
     
     from OP_Order 
     
     where insDT>
     
     '2011-07-22'

 

Timegoeson
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL Server中row_number分页查询的用法详解
01-19
在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。 ROW_NUMBER() 说明：返回结果集分区内行的序列号，每个分区的第一行从...
SQL Server 开窗函数 Over()代替游标的使用详解
12-14
1、与Row_Number() 函数结合使用，对结果进行排序，这个是我们使用的非常多的 　 2、与聚合函数结合使用，利用over子句的分组和排序，对需要的数据进行操作 例如：SUM() Over() 累加值、AVG() Over() 平均数 MAX() ...
ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）
u010199356的博客
03-08 1390
转载自：https://blog.csdn.net/qq_25221835/article/details/82762416，感谢作者的分享 原 ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多） 2018年09月18日 19:11:38 一彡十 ...
SQL--解释：ROW_NUMBER()OVER(PARTITION BY 字段名 ORDER BY字段名 DESC )
Mimo_YY的博客
05-07 784
ROW_NUMBER() OVER (ORDER BY name DESC) 注：是先把name列降序，再为降序以后的每条name记录返回一个序号。 引用：博客园：分组函数 partition by 的详解，与order by 区别 partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录， partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。 partit
ROW_NUMBER() OVER(partition by 分组列 order by 排序列)
woshimyc的专栏
04-26 1122
多用于对数据的快速排序和分组，也可用于分页，简单的逻辑判断。 先创建一个测试用表和数据。 -- Create table create table Test_Orders ( id NUMBER(19) not null, User_Id VARCHAR2(50 CHAR), Total_Price F...
分区函数Partition By的与row_number()的用法以及与排序rank()的用法详解(获取分组(分区)中前几条记录)（转）...
weixin_33704591的博客
06-29 391
转载地址：http://www.cnblogs.com/linJie1930906722/p/6036053.html partition by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，分区函数一般与排名函数一起使用。 准备测试...
Oracle的ROW_NUMBER() OVER()函数
ConstXiong
03-20 1万+
ROW_NUMBER() OVER() 函数的作用：分组排序 原理：row_number() over() 函数，over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。 语法：row_number() over( partition by 分组列 order by 排序列 desc ) 实践： 建立学生数据表 create tab...
SQL Server中row_number函数的常见用法示例详解
12-14
select *，row_number() over(partition by column1 order by column2) as n from tablename 在上面语法中： PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的...
Oracle表中重复数据去重的方法实例详解
12-16
Oracle表中重复数据去重的方法实例详解 我们在项目中肯定会遇到一种情况，就是表中没有主键 有重复数据 或者有主键 但是部分字段有... row_number() over(partition by s.name order by s.id) as nu from mytest s) aa
Mysql实现Oracle中row_number ( ) over ( partition BY ORDER BY DESC )
qq_33883124的博客
05-13 1080
Mysql实现Oracle中row_number ( ) over ( partition BY 字段1 ORDER BY 字段2 DESC ) 例表： 案例1，row_number ( ) over (ORDER BY 字段2 DESC ) Oracle Mysql SELECT b.*, row_number ( ) over ( ORDER BY PRICE DESC ) AS rk ...
row_number() over(partition by order by)
tanghuan0827的博客
12-01 680
row_number() over(partition by 列名1 order by 列名2 desc)的使用 微末6662018-06-04 21:52:1833746收藏17 分类专栏：oracle 表示根据列名1分组，然后在分组内部根据列名2排序，而此函数计算的值就表示每组内部排序后的顺序编号,可以用于去重复值 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码． ...
sql函数--04---ROW_NUMBER() OVER()函数用法详解
时光里的博客
10-26 9123
ROW_NUMBER() OVER()函数 使用范围: 这个函数可以Oracle中使用 MySQL用不了 语法格式： row_number() over(partition by 分组列 order by 排序列 desc) 注意: 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 案例 1 表数据： create table TEST_ROW_NUMBER_OVER(
Oracle中ROW_NUMBER() OVER()函数用法
tnb_ml的博客
09-26 2万+
row_number() over() 函数，over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。row_number() over( partition by 分组列 order by 排序列 desc )3. 获取到每个班级分数排名第一的学生。根据工资排序并添加序号。
row_number(),over()函数组合使用排序取最新一条
枯萎花一朵
12-01 952
partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。
java代码实现ROW_NUMBER ( ) over ( PARTITION BY）语句
qq_37084960的博客
08-26 1127
对List根据某两个字段进行分组排序，排序字段·的类型为Date，即java代码实现ROW_NUMBER ( ) over ( PARTITION BY）语句 public <T> List<T> groupByOrder(List<T> originalList, String groupName, String orderName) throws ...
Oracle使用 row_number() 出现ORA-00923:未找到要求的from 字段
05-26 1148
原语句 select *,row_number() over( order by 'updtime' desc) clm1 from table_name 修改后的语句 select a.*,row_number() over( order by 'updtime' desc) clm1 from table_name a
Oracle之ROW_NUMBER() OVER() 分组排序函数
张大安的博客
02-26 2100
文章目录ROW_NUMBER() OVER() 分组排序 ROW_NUMBER() OVER() 分组排序 语法： row_number() over(partition by 分组列 order by 排序列 desc) rn 注意：在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 常见需求：根据某个字段分组取出最新的一条数据。 原数据： SELECT rir.id, rir.IND
SQL中ROW_NUMBER() OVER()的用法详解
热门推荐
木小南瓜的博客
08-26 3万+
over可以对数据进行分组及排序,执行后将会产生结果集，select会对这些结果集进行编号(每一个结果集都从1开始编号)。 下面看例子： 1.数据表原数据 2.按照添加时间排序，然后给每一条编号 3.此方法还可用于分页，按照添加时间排列，并取第4到第6条数据 通用的分页方法，pageindex表示当前是页码 比如上例子pageindex=2，pagesize=3 select top pagesize * from ( SELECT row_number () O...
ROW_NUMBER() OVER()函数详解
最新发布
09-14
总结起来，ROW_NUMBER() OVER()函数是一种用于为查询结果中的每一行分配唯一序号的窗口函数，可以根据指定的排序规则对结果进行排序，并且可以根据指定的分组规则对结果进行分组。它的执行顺序是在WHERE、GROUP BY和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值