SQL Server递归查询：如何为所有子记录填充最深祖先的vth_pol_id字段

这个需求我之前处理过，核心就是要把分支里最底层（也就是vth_moved_from_vth_id为NULL的根节点）的vth_pol_id值，同步到该分支的所有节点上。下面给你两种最优实现方式：

方法一：用窗口函数快速实现（推荐） #

这种方法最简单直接，利用窗口函数MAX() OVER()提取分支中唯一的非NULLvth_pol_id值，然后替换所有行的对应字段。因为你的场景里每个分支只有根节点有有效vth_pol_id，其他节点都是NULL，所以MAX()会精准拿到根节点的值：

;WITH cte_txn AS (
 SELECT vth_id, vth_pol_id, vth_moved_from_vth_id
 FROM Variant_Transaction_Header
 WHERE vth_id = 72418 -- 指定起始节点
 UNION ALL
 SELECT e.vth_id, e.vth_pol_id, e.vth_moved_from_vth_id
 FROM Variant_Transaction_Header e
 JOIN cte_txn ON cte_txn.vth_moved_from_vth_id = e.vth_id
)
SELECT 
    vth_id, 
    MAX(vth_pol_id) OVER () AS vth_pol_id, -- 替换为分支的根节点pol_id
    vth_moved_from_vth_id
FROM cte_txn;

运行这段代码后，你就能得到想要的结果：所有节点的vth_pol_id都会被替换成根节点的803。

方法二：修改递归CTE传递根节点值（适合复杂场景） #

如果你的分支存在多个非NULLvth_pol_id，需要明确只取根节点的值，那可以在递归过程中跟踪根节点的vth_pol_id，确保每个节点都继承正确的值：

;WITH cte_txn AS (
 -- 锚点查询：初始化根pol_id，标记是否找到根节点
 SELECT 
    vth_id, 
    vth_pol_id, 
    vth_moved_from_vth_id,
    vth_pol_id AS root_pol_id,
    CASE WHEN vth_moved_from_vth_id IS NULL THEN 1 ELSE 0 END AS is_root
 FROM Variant_Transaction_Header
 WHERE vth_id = 72418

 UNION ALL

 SELECT 
    e.vth_id, 
    e.vth_pol_id, 
    e.vth_moved_from_vth_id,
    -- 优先用当前节点的pol_id（如果是根节点），否则继承父节点的根pol_id
    COALESCE(e.vth_pol_id, cte.root_pol_id),
    CASE WHEN e.vth_moved_from_vth_id IS NULL THEN 1 ELSE 0 END AS is_root
 FROM Variant_Transaction_Header e
 JOIN cte_txn ON cte_txn.vth_moved_from_vth_id = e.vth_id
 WHERE cte.is_root = 0 -- 找到根节点后停止递归
)
SELECT 
    vth_id, 
    root_pol_id AS vth_pol_id, 
    vth_moved_from_vth_id
FROM cte_txn;

这种方法通过递归时传递root_pol_id字段，确保所有节点最终都使用根节点的vth_pol_id，逻辑更严谨，适合复杂的分支结构。

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