SQL Server 根据最新日期正确设置ParentKey

eqoofvh9 于 2022-12-26 发布在其他

关注(0)|答案(1)|浏览(114)

我有一个代码可以查找并设置ParentKey。ParentKey从Key字段中获取其值，第一个条件基于公共ProductID。但无法满足正确的第二个条件：ParentKey应该根据Date列中的最近（最大）日期进行设置。如何改进现有代码？

初始表格

+-------------------------------------------------
|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

当前代码

WITH NEW_ID
  AS
  (
  SELECT [Key],  ProductID, [Date],
    ROW_NUMBER() OVER (Partition BY u2.[Date], u2.[Key]
    ORDER BY [Date] ASC) RN -- actually [Date] ASC could be changed to [Date] DESC - the result won't change 

  FROM [MyTable] u2
  ) RN
        

update u        
set u.[ParentKey] = u3.[Key] 
from [MyTable] u
inner join  NEW_ID u3 ON u.ProductID = u3.ProductID and u3.RN=1

sql-server

来源：https://stackoverflow.com/questions/74888210/set-parentkey-correctly-according-the-latest-date

1条答案

按热度按时间

yvt65v4c1#

要直接更正代码，我只需按ProductId添加一个分区...

WITH
  latest AS
(
  SELECT
    ProductID,
    [Date],
    [Key],
    ROW_NUMBER() OVER (
      PARTITION BY ProductID
          ORDER BY [Date] DESC
    )
      AS rn 
  FROM
    [MyTable]
)
UPDATE       
  MyTable
SET
  MyTable.[ParentKey] = latest.[Key] 
FROM
  MyTable
INNER JOIN
  latest
    ON MyTable.ProductID = latest.ProductID
WHERE
  latest.rn = 1

不过，您应该能够进一步简化它，使用可更新的公用表表达式，并使用FIRST_VALUE() ...

WITH
  sorted AS
(
  SELECT
    ProductID,  -- Not strictly needed in the CTE, but it makes me feel more secure
    [Date],     -- Not strictly needed in the CTE, but it makes me feel more secure
    [Key],      -- Not strictly needed in the CTE, but it makes me feel more secure
    ParentKey,
    FIRST_VALUE([Key]) OVER (
      PARTITION BY ProductID
          ORDER BY [Date] DESC
    )
      AS latest_key
  FROM
    [MyTable]
)
UPDATE       
  sorted
SET
  [ParentKey] = latest_key

演示：https://dbfiddle.uk/dcskKHRO

赞(0）回复(0）举报 2022-12-26

我来回答

SQL Server 根据最新日期正确设置ParentKey

1条答案

相关问题

热门标签

最新问答