sql - 获取根元素和所有后代 MS SQL

Question

我应该从该结构中获取所有后代的根元素：

rootA
    rootB
        child1
    rootC
        child2
    chold3
rootD
    child4

结果：

Root| Child

rootA child1
rootA child2
rootA child3
rootD child4

我明白，首先我应该在没有父母的情况下获得根元素：

SELECT DISTINCT
        rootId,
        childId
  FROM
        root
            WHERE rootId IS NULL 

但我不知道下一步。你有一些变种吗？

谢谢！

标签： sqlsql-serverhierarchical-data

Answer 1

这是通过公用表表达式 (CTE) 完成的：

;WITH cte_hierarchy AS (
    -- get all roots
    SELECT DISTINCT
        rootId, childId
    FROM
        root
    WHERE rootId IS NULL

    UNION ALL

    -- this is the recursive part
    -- get all children for all roots, including children of children, etc.
    SELECT
        child.rootId, child.childId
    FROM root child
    JOIN cte_hierarchy parent ON parent.childid=child.rootid
)
SELECT * FROM cte_hierarchy;