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

 

#用法说明

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](50) 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字段就是该订单是第几次购买;

 

posted @ 2019-04-03 22:12  willingtolove  阅读(44617)  评论(2编辑  收藏  举报