我如何获得每个客户的前2个订单

Question

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

SQL代码

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')
--https://www.sqlservertutorial.net/sql-server-basics/sql-server-insert-multiple-rows/

SELECT * 
FROM (
SELECT CustomerId, COUNT(CustomerId) order_count
FROM #Order
GROUP BY CustomerId
) o
LEFT JOIN #Customer c
ON o.CustomerId = c.Id
SELECT * FROM #Order

@@Version

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 
Oct 28 2016 18:17:30 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 19044: )

Answer 1

这就是我的解决方案

SELECT *
FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate desc) OrderNumber
, *
FROM #Order 
) EnumeratedOrders
WHERE OrderNumber <= 2

ROW_NUMBER()函数的详细说明