返回所有子记录的最深祖先的特定列

Question

我正在研究SQL Server中的递归。我有下面的查询，我似乎不能让它做我想要的。

;WITH cte_txn AS (
SELECT
vth_id,
vth_pol_id,
vth_moved_from_vth_id
FROM Variant_Transaction_Header
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 * FROM cte_txn;

如果我在锚中指定一个vth_id(在本例中"WHERE vth_id = 72418")，我得到以下内容:

vth_id  vth_pol_id  vth_moved_from_vth_id
72418   NULL        57019
57019   NULL        53518
53518   803         NULL

这很好。但是现在我想把上面的数据转换成:

vth_id  vth_pol_id  vth_moved_from_vth_id
72418   803         57019
57019   803         53518
53518   803         NULL

换句话说，我想要最深祖先的vth_pol_id，而不管我插入的是哪个vth_id。最好的方法是什么?

编辑:我应该指定我想要获得一个包含所有分支的结果集，其中vth_pol_id本质上表示每个记录在哪个分支上。

Answer 1

我正在尝试从后代到祖先。另一种方式是可取的，因为我需要来自父记录的值与子记录一起使用。下面是更新后的表达式:

;WITH cte_txn AS (
SELECT
vth_id,
vth_pol_id,
vth_moved_from_vth_id
FROM Variant_Transaction_Header
/*start at the base of the branch instead of the end.*/
WHERE NOT vth_pol_id IS NULL 
UNION ALL
SELECT
e.vth_id,
/* use parent's vth_pol_id */
cte_txn.vth_pol_id,
e.vth_moved_from_vth_id
FROM Variant_Transaction_Header e
/* Move away from ancestor instead of toward. */
JOIN cte_txn ON cte_txn.vth_id = e.vth_moved_from_vth_id
WHERE cte_txn.vth_id <> e.vth_id
)
/* Join again with vth for all records */
SELECT
v.vth_id,
c.vth_pol_id,
v.vth_moved_from_vth_id
FROM Variant_Transaction_Header v
LEFT OUTER JOIN cte_txn c ON c.vth_id = v.vth_id

外部连接是为了确保如果根节点碰巧在vth_pol_id列中具有空值，则仍然返回所有记录。简单:

SELECT * FROM cte_txn;

如果要省略此类记录，则可以使用。