如何在SSMS中通过递归自连接从子节点追溯所有父节点

没问题，我来帮你调整递归CTE实现这个需求！

核心思路 #

原来的CTE是从顶层父节点向下遍历所有子节点，现在我们要改成从指定子节点向上遍历所有父节点，同时给每个节点标记层级：目标子节点层级为0，它的父节点层级为1，父节点的父节点层级为2……最后按层级倒序排列，就能得到从顶层父节点到目标子节点的顺序。

实现代码 #

这里以你提到的族谱表为例，假设要查询ID=50的所有父节点，代码如下：

DECLARE @TargetChildID INT = 50; -- 替换成你需要查询的子节点ID

WITH AncestorCTE AS (
    -- 锚点查询：选中目标子节点，初始化层级为0
    SELECT 
        ID, 
        ParentNodeID, 
        Name,
        0 AS NodeLevel
    FROM [QA].[Node] 
    WHERE ID = @TargetChildID

    UNION ALL

    -- 递归查询：向上查找当前节点的父节点，层级+1
    SELECT 
        parent.ID, 
        parent.ParentNodeID, 
        parent.Name,
        child.NodeLevel + 1 AS NodeLevel
    FROM [QA].[Node] parent
    INNER JOIN AncestorCTE child ON parent.ID = child.ParentNodeID
)
-- 按层级倒序排序，顶层父节点在前，目标子节点在后
SELECT ID, ParentNodeID, Name
FROM AncestorCTE
ORDER BY NodeLevel DESC;

代码说明 #

1. 锚点部分：先定位到你指定的子节点，同时新增NodeLevel字段，把这个子节点的层级设为0。
2. 递归部分：通过parent.ID = child.ParentNodeID关联父节点，并且让父节点的层级比子节点大1，这样越往上的祖先节点层级数值越大。
3. 排序逻辑：最后用ORDER BY NodeLevel DESC，层级最大的顶层父节点会排在最前面，依次往下直到层级为0的目标子节点，完全符合你要的输出顺序。

效果验证 #

用你给出的族谱例子（ID=50，父节点99，99的父节点1），执行后会得到：

完全匹配你期望的结果！如果需要动态传入子节点ID，把@TargetChildID换成存储过程参数或者直接替换成具体数值即可。

内容的提问来源于stack exchange，提问作者A.Mills