我有一个层次结构树形表格,如下:
使用CTE递归表,如何获取所选节点的路径和所有子节点。例如,如果输入为
我知道我可以通过以下语句在树中向上遍历(获取路径):
以下语句可以在树形结构中向下遍历(获取所有孩子节点):
GO
DROP TABLE #tbl
GO
CREATE TABLE #tbl (Id int , ParentId int)
INSERT INTO #tbl (Id, ParentId) VALUES (0, NULL)
INSERT INTO #tbl (Id, ParentId) VALUES (1, 0)
INSERT INTO #tbl (Id, ParentId) VALUES (2, 1)
INSERT INTO #tbl (Id, ParentId) VALUES (3, 1)
INSERT INTO #tbl (Id, ParentId) VALUES (4, 2)
INSERT INTO #tbl (Id, ParentId) VALUES (5, 2)
INSERT INTO #tbl (Id, ParentId) VALUES (6, 3)
INSERT INTO #tbl (Id, ParentId) VALUES (7, 3)
GO
对应以下树形结构:
0
+- 1
+- 2
+- 4
+- 5
+- 3
+- 6
+- 7
使用CTE递归表,如何获取所选节点的路径和所有子节点。例如,如果输入为
2
,我该如何获得以下数据(如果可能,按顺序排列)Id, ParentID
-------
0, NULL
1, 0
2, 1
4, 2
5, 2
我知道我可以通过以下语句在树中向上遍历(获取路径):
WITH RecursiveTree AS (
-- Anchor
SELECT *
FROM #tbl
WHERE Id = 2
UNION ALL
-- Recursive Member
SELECT Parent.*
FROM
#tbl AS Parent
JOIN RecursiveTree AS Child ON Child.ParentId = Parent.Id
)
SELECT * FROM RecursiveTree
以下语句可以在树形结构中向下遍历(获取所有孩子节点):
WITH RecursiveTree AS (
-- Anchor
SELECT *
FROM #tbl
WHERE Id = 2
UNION ALL
-- Recursive Member
SELECT Child.*
FROM
#tbl AS Child
JOIN RecursiveTree AS Parent ON Child.ParentId = Parent.id
)
SELECT * FROM RecursiveTree
问题:如何将这两个命令合并为一个命令?