SQL树遍历-网格节点

Question

我的数据库中有以下数据:

Parent      Child
======      =====
1           2
1           3
2           4
2           5
3           6
5           7

我想检索这棵树的所有子代和子代。我可以用with递归使用它吗?

输出应该是:

Parent      Child
======      =====
1           2
1           3
1           4
1           5
1           7
2           4
2           5
2           7
3           6
5           7

谢谢。

Answer 1

WITH cte AS (
  SELECT parent, child, 1 AS depth
  FROM MyTable
  UNION ALL
  SELECT t1.parent, t2.child, depth+1
  FROM cte t1
  INNER JOIN @t t2 ON (t2.parent = t1.child)
  WHERE depth < 2
)
SELECT parent, child 
FROM cte