SQL Server如何查询每个客户最新的前2条订单记录

• SQL Server 2016 及更高版本

• 如何在订单表中查询每个客户最近的2条订单记录？

CustomerId  order_count Id          FirstName                                          City
----------- ----------- ----------- -------------------------------------------------- --------------------------------------------------
1           3           1           Rodney                                             Augusta
2           3           2           Autumn                                             Mobile

(2 rows affected)

Id          CustomerId  OrderDate               ProductName
----------- ----------- ----------------------- --------------------------------------------------
1           1           2022-04-26 10:00:00.000 RodneyProduct1
2           2           2022-04-27 11:00:00.000 Autumn Product 1
3           1           2022-04-28 09:11:42.933 RodneyProduct2
4           1           2022-04-01 09:13:13.447 RodneyProduct3
5           2           2022-04-02 09:14:13.447 Autumn Product 2
6           2           2022-04-03 09:15:13.447 Autumn Product 3

Id          CustomerId  OrderDate               ProductName
----------- ----------- ----------------------- --------------------------------------------------
3           1           2022-04-28 09:11:42.933 RodneyProduct2
1           1           2022-04-26 10:00:00.000 RodneyProduct1
2           2           2022-04-27 11:00:00.000 Autumn Product 1
6           2           2022-04-03 09:15:13.447 Autumn Product 3

使用ROW_NUMBER()窗口函数实现分组取Top N需求，按客户ID分区、订单日期倒序生成序号，筛选序号小于等于2的记录即可。

实现代码 #

替换原有最后两段查询逻辑，执行以下语句即可得到期望结果：

SELECT Id, CustomerId, OrderDate, ProductName
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY OrderDate DESC) AS row_rank
    FROM #Order
) temp
WHERE row_rank <= 2
ORDER BY CustomerId, OrderDate DESC

语法说明 #

• PARTITION BY CustomerId：将订单数据按客户ID拆分分组，窗口函数计算范围限定在单个客户的订单集合内
• ORDER BY OrderDate DESC：每个客户的订单按下单时间从新到旧排序
• ROW_NUMBER()为排序后的记录生成从1开始的连续序号，最新订单序号为1，次新为2，以此类推
• 外层筛选row_rank <=2即可提取每个客户最新的2条订单，最终排序规则和期望输出完全匹配

附：测试表初始化完整代码 #

CREATE TABLE #Customer(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE #Order(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NULL,
    [OrderDate] [datetime] NULL,
    [ProductName] [nvarchar](50) NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE #Order ADD  CONSTRAINT [DF_Order_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
GO

SET IDENTITY_INSERT #Customer ON 
GO
INSERT #Customer ([Id], [FirstName], [City]) VALUES (1, N'Rodney', N'Augusta')
GO
INSERT #Customer ([Id], [FirstName], [City]) VALUES (2, N'Autumn', N'Mobile')
GO
SET IDENTITY_INSERT #Customer OFF
GO

SET IDENTITY_INSERT #Order ON 
GO
INSERT #Order ([Id], [CustomerId], [OrderDate], [ProductName]) 
VALUES 
(1, 1, CAST(N'2022-04-26T10:00:00.000' AS DateTime), N'RodneyProduct1')
,(2, 2, CAST(N'2022-04-27T11:00:00.000' AS DateTime), N'Autumn Product 1')
,(3, 1, CAST(N'2022-04-28T09:11:42.933' AS DateTime), N'RodneyProduct2')
,(4, 1, CAST(N'2022-04-01T09:13:13.447' AS DateTime), N'RodneyProduct3')
,(5, 2, CAST(N'2022-04-02T09:14:13.447' AS DateTime), N'Autumn Product 2')
,(6, 2, CAST(N'2022-04-03T09:15:13.447' AS DateTime), N'Autumn Product 3')
GO
SET IDENTITY_INSERT #Order OFF
GO

兼容性说明 #

当前使用的SQL Server 2016 SP1版本原生支持ROW_NUMBER()窗口函数语法，无需额外适配，上述代码可直接执行。

内容的提问来源于stack exchange，提问作者Rod