SQL查询获取每位客户最近及倒数第二笔订单日期方法

编写查询语句，查询每位客户的最近一笔下单日期、以及倒数第二笔下单日期，需返回字段：FirstName、LastName、CustomerID、SalesOrderID、最近订单日期、倒数第二笔订单日期。

预期查询结果示例：

• 子查询关联别名错误：外层过滤逻辑的子查询中引用了未定义的别名c，会直接触发语法报错
• 窗口函数排序方向错误：LAG函数配套的排序规则为订单日期升序，无法保证取到的上一条记录对应倒数第二笔新订单
• 分区字段选择不合理：用PersonID做分区字段不符合业务逻辑，单个主体可能绑定多个客户ID，会导致跨客户的订单数据被错误归为同一组
• 最新订单定位逻辑有风险：用MAX(LastOrder)匹配的方式，在客户存在多笔同时间最新订单时会返回重复结果，性能也低于窗口函数直接打序号的方案

WITH CTE AS
(
    SELECT 
        S.SalesOrderID, 
        C.CustomerID, 
        p.LastName, 
        p.FirstName, 
        S.OrderDate AS LastOrder,
        LAG(S.OrderDate, 1) OVER (
            PARTITION BY C.CustomerID 
            ORDER BY S.OrderDate DESC
        ) AS PreviousOrder,
        ROW_NUMBER() OVER (
            PARTITION BY C.CustomerID 
            ORDER BY S.OrderDate DESC, S.SalesOrderID DESC
        ) AS OrderRank
    FROM sales.SalesOrderHeader S 
    INNER JOIN Sales.Customer C 
        ON S.CustomerID = C.CustomerID
    INNER JOIN Person.Person p 
        ON P.BusinessEntityID = C.PersonID
)
SELECT 
    FirstName,
    LastName,
    CustomerID,
    SalesOrderID,
    LastOrder AS 最近订单日期,
    PreviousOrder AS 倒数第二笔订单日期
FROM CTE
WHERE OrderRank = 1

• 对每个客户的订单按下单日期倒序排序，追加SalesOrderID倒序作为排序兜底规则，避免同日期订单排序随机
• 用ROW_NUMBER()给每个客户的订单打排名，排名为1的就是该客户的最新一笔订单
• 用LAG()在同分区内取排名第一的订单的上一条记录日期，就是该客户的倒数第二笔下单日期，没有历史订单的客户该字段返回NULL
• 最终只过滤出排名为1的记录，就能一次性拿到所有要求的字段，不需要额外嵌套子查询做关联

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