【SQL】ROW_NUMBER() OVER(partition by 分组列 order by 排序列)用法详解+经典实例

简介: 【SQL】ROW_NUMBER() OVER(partition by 分组列 order by 排序列)用法详解+经典实例目录0、填充数据1、使用row_number()函数对订单进行编号,按照订单时间倒序。

【SQL】ROW_NUMBER() OVER(partition by 分组列 order by 排序列)用法详解+经典实例
目录

0、填充数据
1、使用row_number()函数对订单进行编号,按照订单时间倒序。(此需求多用于分页)
2、所有订单按照客户进行分组,并按照客户下的订单的金额倒序排列。
3、筛选出客户第一次下的订单。
4、筛选出客户在‘2011年1月1日之后的第一次下的订单。
5、只保留每个客户的最近的一次订单,其余的订单删掉。(常用于删除重复数据)
6、统计每一个客户所有的订单中金额最大,并统计该订单是客户第几次购买;

正文

用法说明

select row_number() over(partition by A order by B ) as rowIndex from table

  A :为分组字段

  B:为分组后的排序字段。

  table 表的结构 多为: 多人 多条的相关数据。(比如:订单信息)

  此条sql语句,多用于对数据进行分组排序,并对每个组中的数据分别进行编号,编号从1开始递增,每个组内的编号不会重复;

经典实例

回到顶部
0、填充数据
复制代码
1 create table OrderInfo NOT NULL,
3 [UserId] nvarchar NOT NULL,
4 [TotalPrice] [float] NOT NULL,
5 [OrderTime] [datetime] NOT NULL,
6 );
7
8 INSERT INTO [dbo].[OrderInfo]
9 ([UserId]
10 ,[TotalPrice]
11 ,[OrderTime])
12 VALUES
13 (N'1', 111, CAST(N'2011-01-01' AS DateTime)),
14 (N'1', 112, CAST(N'2011-01-02' AS DateTime)),
15 (N'3', 311, CAST(N'2013-01-01' AS DateTime)),
16 (N'3', 312, CAST(N'2013-01-02' AS DateTime)),
17 (N'2', 211, CAST(N'2012-01-01' AS DateTime)),
18 (N'2', 212, CAST(N'2012-01-02' AS DateTime)),
19 (N'1', 113, CAST(N'2011-01-03' AS DateTime)),
20 (N'2', 213, CAST(N'2012-01-03' AS DateTime)),
21 (N'3', 313, CAST(N'2013-01-03' AS DateTime))
22 GO
复制代码
回到顶部
1、使用row_number()函数对订单进行编号,按照订单时间倒序。(此需求多用于分页)
1 select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER() over (order by OrderTime desc) as rowIndex from OrderInfo

分页场景:每页3条数据,取第2页

复制代码
1 with
2 baseDate
3 as
4 (
5 select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER() over (order by OrderTime desc) as rowIndex from OrderInfo
6 )
7 select * from baseDate where rowIndex>3 and rowIndex<7
复制代码
回到顶部
2、所有订单按照客户进行分组,并按照客户下的订单的金额倒序排列。
1 select Id,UserId,orderTime,ROW_NUMBER() over(partition by UserId order by TotalPrice desc) as rowIndex from OrderInfo

回到顶部
3、筛选出客户第一次下的订单。
  思路:利用rowIndex来判断订单是客户第几次下单;

复制代码
1 with
2 baseDate
3 as
4 (
5 select Id,UserId,TotalPrice,orderTime,ROW_NUMBER() over (partition by UserId order by orderTime) as rowIndex from OrderInfo
6 )
7 select * from baseDate where rowIndex=1
复制代码

回到顶部
4、筛选出客户在‘2011年1月1日之后的第一次下的订单。
  思路:在分组排序之前进行实践筛选;

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

复制代码
1 with
2 baseDate
3 as
4 (
5 select Id,UserId,TotalPrice,orderTime,ROW_NUMBER() over (partition by UserId order by orderTime) as rowIndex from OrderInfo
6 where OrderTime>'2011-1-1'
7 )
8 select * from baseDate where rowIndex=1
复制代码

回到顶部
5、只保留每个客户的最近的一次订单,其余的订单删掉。(常用于删除重复数据)
复制代码
1 with
2 baseDate
3 as
4 (
5 select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER()over (partition by UserId order by OrderTime desc) as rowIndex from OrderInfo
6 )
7 delete from baseDate where rowIndex <> 1
复制代码
回到顶部
6、统计每一个客户所有的订单中金额最大,并统计该订单是客户第几次购买;
  思路:

    1)先按照客户进行分组,然后按照客户下单的时间进行正序排列,并编号(rowIndex),生成临时表baseDate;

    2)再按照客户进行分组,然后按照客户下单的金额进行倒序排列,并编号(rowIndex),生成临时表basePrice;

    3)最后取basePrice中编号为1的数据,然后根据id到baseDate中去查,即可;

复制代码
1 with
2 baseDate
3 as
4 (
5 select Id,UserId,TotalPrice,orderTime,ROW_NUMBER() over (partition by UserId order by orderTime) as rowIndex from OrderInfo
6 ),
7 basePrice
8 as
9 (
10 select Id,UserId,orderTime,ROW_NUMBER() over(partition by UserId order by TotalPrice desc) as rowIndex from OrderInfo
11 )
12 select * from baseDate
13 where Id in (
14 select Id from basePrice where rowIndex=1
15 )
复制代码

图中的rowIndex字段就是该订单是第几次购买;

作者:willingtolove

出处:http://www.cnblogs.com/willingtolove/

相关文章
|
2月前
|
SQL 关系型数据库 MySQL
SQL中,可以使用 `ORDER BY` 子句来实现排序功能
【10月更文挑战第26天】SQL中,可以使用 `ORDER BY` 子句来实现排序功能
203 6
|
7月前
|
SQL 存储 安全
sql server 数据库实例
SQL Server 数据库实例是指在 SQL Server 中创建的一个独立的数据库环境。每个数据库实例都拥有自己的一套完整的数据库文件、配置设置、用户和权限等,可以独立地进行管理和操作。以下是关于
|
3月前
|
SQL 数据管理 数据库
SQL语句实例教程:掌握数据查询、更新与管理的关键技巧
SQL(Structured Query Language,结构化查询语言)是数据库管理和操作的核心工具
|
3月前
|
SQL 存储 数据采集
sql中varchar转number时报错怎么解决
通过上述策略的综合运用,面对VARCHAR到NUMBER的转换挑战,不仅能够游刃有余地解决现有的问题,更能前瞻性地预防未来的隐患。在数字化转型的浪潮中,凭借其高性能、安全稳定的云产品与服务,为各类企业级应用保驾护航,助您轻松驾驭数据的海洋,实现业务的无缝扩展与全球布局。
76 0
|
6月前
|
SQL 索引
SQL DELETE 实例
【7月更文挑战第12天】SQL DELETE 实例。
56 12
|
6月前
|
SQL 分布式计算 大数据
MaxCompute操作报错合集之执行多条SQL语句时,使用同一个实例来运行,遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
6月前
|
SQL 索引
SQL DELETE 实例
【7月更文挑战第13天】SQL DELETE 实例。
59 6
|
6月前
|
SQL
SQL LIKE 操作符实例
【7月更文挑战第13天】SQL LIKE 操作符实例
47 5
|
6月前
|
SQL 数据库
SQL ORDER BY 关键字
【7月更文挑战第11天】SQL ORDER BY 关键字。
46 2
|
7月前
|
SQL Java 数据处理
SQL游标的详细使用方法与实例
SQL游标的详细使用方法与实例

热门文章

最新文章