如何在查询结果中返回每个LeadRecipientID的最新UpdatedDate

原查询的问题在于同时按LeadRecipientID和LeadPayorID分组，导致同一收款方对应不同付款方时会返回多条记录。要实现每个LeadRecipientID仅保留UpdatedDate最新的单条记录，可以使用窗口函数ROW_NUMBER()来解决：

解决方案代码 #

WITH RankedRecords AS (
    SELECT 
        LeadPayorID,
        LeadRecipientID,
        UpdatedDate,
        -- 按收款方分区，每个分区内按更新日期降序排序并分配序号
        ROW_NUMBER() OVER (PARTITION BY LeadRecipientID ORDER BY UpdatedDate DESC) AS rn
    FROM LeadBilling
    WHERE LeadRecipientID != LeadPayorID
)
-- 筛选每个收款方的第一条（最新）记录
SELECT LeadPayorID, LeadRecipientID, UpdatedDate
FROM RankedRecords
WHERE rn = 1;

代码说明 #

1. 分区排序：通过PARTITION BY LeadRecipientID将数据按收款方分组，每个分组内按UpdatedDate DESC从最新到最旧排序，用ROW_NUMBER()给每条记录分配唯一序号rn，最新的记录序号为1。
2. 筛选结果：最后只保留序号为1的记录，即每个收款方的最新更新记录。

执行该查询后，会完全匹配你期望的结果：每个LeadRecipientID仅返回一条对应最新UpdatedDate的记录，自动剔除同一收款方下较旧的记录（比如LeadRecipientID=640会只保留2024-09-26的记录，LeadRecipientID=644只保留2024-09-26的记录）。

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