- # 用法说明
- select row_number() over(partition by A order by B ) as rowIndex from table
A : 为分组字段
B: 为分组后的排序字段.
table 表的结构 多为: 多人 多条的相关数据.(比如: 订单信息)
此条 sql 语句, 多用于对数据进行分组排序, 并对每个组中的数据分别进行编号, 编号从 1 开始递增, 每个组内的编号不会重复;
# 经典实例
0, 填充数据
- create table [OrderInfo](
- [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
- [UserId] [nvarchar](50) NOT NULL,
- [TotalPrice] [float] NOT NULL,
- [OrderTime] [datetime] NOT NULL,
- );
- INSERT INTO [dbo].[OrderInfo]
- ([UserId]
- ,[TotalPrice]
- ,[OrderTime])
- VALUES
- (N'1', 111, CAST(N'2011-01-01' AS DateTime)),
- (N'1', 112, CAST(N'2011-01-02' AS DateTime)),
- (N'3', 311, CAST(N'2013-01-01' AS DateTime)),
- (N'3', 312, CAST(N'2013-01-02' AS DateTime)),
- (N'2', 211, CAST(N'2012-01-01' AS DateTime)),
- (N'2', 212, CAST(N'2012-01-02' AS DateTime)),
- (N'1', 113, CAST(N'2011-01-03' AS DateTime)),
- (N'2', 213, CAST(N'2012-01-03' AS DateTime)),
- (N'3', 313, CAST(N'2013-01-03' AS DateTime))
- GO
1, 使用 row_number() 函数对订单进行编号, 按照订单时间倒序.(此需求多用于分页)
1 select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER() over (order by OrderTime desc) as rowIndex from OrderInfo
- # 分页场景: 每页 3 条数据, 取第 2 页
- with
- baseDate
- as
- (
- select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER() over (order by OrderTime desc) as rowIndex from OrderInfo
- )
- 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 来判断订单是客户第几次下单;
- with
- baseDate
- as
- (
- select Id,UserId,TotalPrice,orderTime,ROW_NUMBER() over (partition by UserId order by orderTime) as rowIndex from OrderInfo
- )
- select * from baseDate where rowIndex=1
4, 筛选出客户在'2011 年 1 月 1 日之后的第一次下的订单.
思路: 在分组排序之前进行实践筛选;
注意: 在使用 over 等开窗函数时, over 里头的分组及排序的执行晚于 "where,group by,order by" 的执行.
- with
- baseDate
- as
- (
- select Id,UserId,TotalPrice,orderTime,ROW_NUMBER() over (partition by UserId order by orderTime) as rowIndex from OrderInfo
- where OrderTime>'2011-1-1'
- )
- select * from baseDate where rowIndex=1
5, 只保留每个客户的最近的一次订单, 其余的订单删掉.(常用于删除重复数据)
- with
- baseDate
- as
- (
- select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER()over (partition by UserId order by OrderTime desc) as rowIndex from OrderInfo
- )
- delete from baseDate where rowIndex <> 1
6, 统计每一个客户所有的订单中金额最大, 并统计该订单是客户第几次购买;
思路:
1) 先按照客户进行分组, 然后按照客户下单的时间进行正序排列, 并编号 (rowIndex), 生成临时表 baseDate;
2) 再按照客户进行分组, 然后按照客户下单的金额进行倒序排列, 并编号 (rowIndex), 生成临时表 basePrice;
3) 最后取 basePrice 中编号为 1 的数据, 然后根据 id 到 baseDate 中去查, 即可;
- with
- baseDate
- as
- (
- select Id,UserId,TotalPrice,orderTime,ROW_NUMBER() over (partition by UserId order by orderTime) as rowIndex from OrderInfo
- ),
- basePrice
- as
- (
- select Id,UserId,orderTime,ROW_NUMBER() over(partition by UserId order by TotalPrice desc) as rowIndex from OrderInfo
- )
- select * from baseDate
- where Id in (
- select Id from basePrice where rowIndex=1
- )
# 图中的 rowIndex 字段就是该订单是第几次购买;
来源: https://www.cnblogs.com/willingtolove/p/10623841.html