如何修改SQL代码按最新Date设置同ProductID的ParentKey？

现有SQL代码已实现按相同ProductID从Key字段取值，但无法满足ParentKey需取对应ProductID下最新（最大）Date记录的Key值的要求，以下是具体改进方案：

初始表数据 #

+-------------------------------------------------
|ProductID  |   Date        |  Key   |  ParentKey 
+-------------------------------------------------
|111        |   2017-12-31  |   1    |   1
|111        |   2018-12-31  |   2    |   1
|111        |   2019-12-31  |   3    |   1
|111        |   2020-12-31  |   4    |   1
|222        |   2017-12-31  |   5    |   5
|222        |   2018-12-31  |   6    |   5
|222        |   2019-12-31  |   7    |   5
|222        |   2020-12-31  |   8    |   5
|333        |   2017-12-31  |   9    |   9
|333        |   2018-12-31  |   10   |   9
|333        |   2019-12-31  |   11   |   9
|333        |   2020-12-31  |   12   |   9

期望输出数据 #

+-------------------------------------------------
|ProductID  |   Date        |  Key   |  ParentKey 
+-------------------------------------------------
|111        |   2017-12-31  |   1    |   4
|111        |   2018-12-31  |   2    |   4
|111        |   2019-12-31  |   3    |   4
|111        |   2020-12-31  |   4    |   4
|222        |   2017-12-31  |   5    |   8
|222        |   2018-12-31  |   6    |   8
|222        |   2019-12-31  |   7    |   8
|222        |   2020-12-31  |   8    |   8
|333        |   2017-12-31  |   9    |   12
|333        |   2018-12-31  |   10   |   12
|333        |   2019-12-31  |   11   |   12
|333        |   2020-12-31  |   12   |   12

当前代码问题分析 #

原CTE的PARTITION BY u2.[Date], u2.[Key]分组逻辑错误，它会把每个Date+Key的组合单独分组，无法按ProductID聚合最新日期的记录；排序逻辑也未针对日期倒序取最新值。

改进后的代码 #

方案1：使用ROW_NUMBER()窗口函数 #

WITH LatestProductKey AS (
    SELECT 
        ProductID,
        [Key],
        ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY [Date] DESC) AS RN
    FROM [MyTable]
)
UPDATE u
SET u.[ParentKey] = lp.[Key]
FROM [MyTable] u
INNER JOIN LatestProductKey lp 
    ON u.ProductID = lp.ProductID 
    AND lp.RN = 1;

方案2：使用MAX(Date)关联（更简洁） #

UPDATE u
SET u.[ParentKey] = lp.[Key]
FROM [MyTable] u
INNER JOIN (
    SELECT 
        ProductID,
        [Key]
    FROM [MyTable] t1
    WHERE [Date] = (SELECT MAX([Date]) FROM [MyTable] t2 WHERE t2.ProductID = t1.ProductID)
) lp ON u.ProductID = lp.ProductID;

代码说明 #

• 方案1：通过PARTITION BY ProductID按产品分组，ORDER BY [Date] DESC让最新日期的记录排在组内第一位（RN=1），关联后将该记录的Key赋值给同ProductID所有行的ParentKey。
• 方案2：子查询先找出每个ProductID的最大日期，再匹配对应Key，直接关联更新，逻辑更直观。

内容的提问来源于stack exchange，提问作者XSLT-explorer