获取根元素和所有后代 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 

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

谢谢！

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;