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

【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](
2 [Id] [int] PRIMARY KEY IDENTITY(1,1) 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 )
复制代码