如何在创建SQL视图时同时生成虚拟Key与ParentKey列？

需求背景 #

原表MyTable原本通过以下UPDATE语句为ParentKey赋值：

WITH
  latest
  as
(
  SELECT
   ProductID,
   Date,
   [Key],
    ROW_NUMBER() OVER (
      PARTITION BY  ProductID
          ORDER BY [Date] DESC -- 按最新日期排序
    ) rn 
  FROM
   [MyTable]
)

UPDATE      
  u 
SET
  u.[ParentKey] = latest.[Key] 
FROM
   [MyTable] u
INNER JOIN
  latest
    ON u.ProductID = latest.ProductID
WHERE
  latest.rn = 1

逻辑是每个ProductID组内，将最新日期对应行的Key设为该组所有行的ParentKey。现在需要直接在视图中生成虚拟计算列Key和ParentKey，无需预先更新表，最终输出需匹配示例格式。

解决方案 #

可以通过CTE（公共表表达式）分步骤实现，以下提供两种可行写法：

写法1：分组取最大Key作为ParentKey

CREATE VIEW v_Test 
AS
-- 第一步：生成所有行的Key（按ProductID+Date排序）
WITH AllRowsWithKey AS (
    SELECT
        ProductID,
        Date,
        CAST(ROW_NUMBER() OVER(ORDER BY ProductID, Date) AS int) AS [Key]
    FROM [MyTable]
),
-- 第二步：按ProductID分组，取每组最大的Key（即最新日期行的Key）
ProductLatestKey AS (
    SELECT
        ProductID,
        MAX([Key]) AS ParentKey
    FROM AllRowsWithKey
    GROUP BY ProductID
)
-- 关联两个CTE，输出最终结果
SELECT
    ar.ProductID,
    ar.Date,
    ar.[Key],
    pl.ParentKey
FROM AllRowsWithKey ar
INNER JOIN ProductLatestKey pl ON ar.ProductID = pl.ProductID;

写法2：用窗口函数直接计算ParentKey

CREATE VIEW v_Test 
AS
WITH AllRowsWithKey AS (
    SELECT
        ProductID,
        Date,
        -- 生成Key列
        CAST(ROW_NUMBER() OVER(ORDER BY ProductID, Date) AS int) AS [Key],
        -- 标记每个ProductID组内最新日期的行（rn=1）
        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY Date DESC) AS rn
    FROM [MyTable]
)
SELECT
    ProductID,
    Date,
    [Key],
    -- 提取组内rn=1的Key作为整个组的ParentKey
    MAX(CASE WHEN rn = 1 THEN [Key] END) OVER(PARTITION BY ProductID) AS ParentKey
FROM AllRowsWithKey;

说明 #

两种写法均能实现需求：

• Key列按ProductID+Date排序生成行号，确保同一ProductID下日期越晚，Key值越大；
• ParentKey列自动取对应ProductID组内最新日期行的Key，与原UPDATE逻辑完全一致；
• 所有计算在视图查询时实时完成，属于虚拟列，无需修改原表数据。

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