如何在SQL Server中通过递归查询获取父子表的根节点及所有后继节点

要实现每个节点作为根节点，返回自身及所有层级后继节点的需求，可以使用MS SQL Server支持的**递归公用表表达式（CTE）**来完成，以下是具体实现方案：

完整SQL代码 #

假设你的父子关系表名为HierarchyTable，代码如下：

-- 先收集所有存在的节点（包括父节点和子节点）
WITH AllNodes AS (
    SELECT Parent AS Node FROM HierarchyTable
    UNION
    SELECT Child AS Node FROM HierarchyTable
),
-- 递归遍历每个节点的所有后继
RecursiveHierarchy AS (
    -- 锚点成员：每个节点自身作为根节点的第一个后继
    SELECT 
        Node AS Root,
        Node AS Successor
    FROM AllNodes
    -- 递归成员：逐层获取当前节点的子节点，直到没有更深层级
    UNION ALL
    SELECT 
        rh.Root,
        ht.Child AS Successor
    FROM RecursiveHierarchy rh
    JOIN HierarchyTable ht ON rh.Successor = ht.Parent
)
-- 输出结果并排序
SELECT 
    Root,
    Successor
FROM RecursiveHierarchy
ORDER BY Root, Successor;

代码逻辑说明 #

1. AllNodes CTE：

   • 通过UNION合并表中Parent和Child列的所有值，得到所有存在的节点（包括叶子节点，比如示例中的A11、A211），确保每个节点都能作为Root出现在结果中。

2. RecursiveHierarchy CTE：

   • 锚点成员：将每个节点同时作为Root和Successor，满足每个节点自身必须出现在结果中的要求。
   • 递归成员：通过关联原表，把当前的Successor作为父节点，找到对应的子节点，持续递归直到遍历完所有层级的后继节点。

3. 最终查询：

   • 直接从递归CTE中取出结果并排序，得到和你期望一致的输出。

测试示例数据 #

如果需要验证，可以先创建测试表并插入示例数据：

CREATE TABLE HierarchyTable (Parent VARCHAR(10), Child VARCHAR(10));
INSERT INTO HierarchyTable VALUES
('A', 'A1'),
('A1', 'A11'),
('A', 'A2'),
('A2', 'A21'),
('A21', 'A211');

运行递归查询后，就能得到你给出的期望结果。

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