无需CTE或临时表，如何按分组获取首个订单商品？

订单数据 #

现有如下订单列表：

对应的插入脚本：

DECLARE @Orders TABLE (OrderId BIGINT NOT NULL IDENTITY(1,1), CustomerId BIGINT NOT NULL, 
                       ItemOrdered varchar(300) NOT NULL, OrderedWhen DATETIME NOT NULL)
INSERT INTO @Orders VALUES 
(1,'Orange', '2024-08-01 16:33'),
(1,'Apple', '2022-01-28 01:00'),
(1,'Peach', '2025-06-10 03:39'),
(1,'Banana', '2022-01-28 01:00'),
(2,'Kiwi', '2021-12-21 11:31'),
(2,'Apple', '2025-02-08 01:00'),
(3,'Strawberry', '2024-05-16 01:30'),
(3,'Banana', '2025-02-1 05:29')

需求 #

针对每个CustomerId，按OrderedWhen最早优先，OrderedWhen相同时ItemOrdered字母顺序优先的规则，获取对应的完整订单记录，期望输出如下：

注意：当OrderedWhen相同时，Apple因字母序优先于Banana被选中。

尝试的方法 #

失败的SQL语句 #

曾尝试以下语句，但因OrderedWhen未包含在GROUP BY中无法编译：

SELECT  ord.CustomerId, MIN(ord.ItemOrdered) OVER (PARTITION BY ord.CustomerId ORDER BY MIN(ord.OrderedWhen)) AS MostRecentItemOrdered
FROM    @Orders ord
GROUP BY ord.CustomerId
ORDER BY ord.CustomerId

可行但需要临时表的方法 #

使用临时表实现了需求，但希望无需拆分语句（不使用CTE或临时表）：

SELECT  ord.CustomerId, MIN(ord.OrderedWhen) AS MostRecentOrderWhen
INTO    #MostRecentOrderWhen
FROM    @Orders ord
GROUP BY ord.CustomerId
ORDER BY ord.CustomerId

SELECT  ord.CustomerId, MIN(ord.ItemOrdered) AS ItemOrdered, ord.OrderedWhen
FROM    #MostRecentOrderWhen recent
        JOIN @Orders ord
            ON recent.CustomerId = ord.CustomerId
            AND recent.MostRecentOrderWhen = ord.OrderedWhen
GROUP BY ord.CustomerId, ord.OrderedWhen
ORDER BY ord.CustomerId

提问 #

是否可以不使用CTE或临时表，用单条SQL语句实现该需求？

可以使用ROW_NUMBER()窗口函数直接实现，通过分区和排序规则标记目标记录，再筛选出标记为1的行即可：

SELECT OrderId, CustomerId, ItemOrdered, OrderedWhen
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerId 
            ORDER BY OrderedWhen ASC, ItemOrdered ASC
        ) AS rn
    FROM @Orders
) t
WHERE rn = 1
ORDER BY CustomerId

说明 #

• PARTITION BY CustomerId：按客户ID分组处理
• ORDER BY OrderedWhen ASC, ItemOrdered ASC：先按下单时间升序（最早优先），时间相同时按商品名称字母升序（字母序优先）
• ROW_NUMBER()会为每个分组内的行按指定规则编号，编号为1的就是我们需要的目标记录
• 外层查询筛选rn=1的行，即可得到每个客户符合要求的首条订单记录

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