使用SQL 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
问题：如何将这两个命令合并为一个？
只需使用这两个选择的联合
SQLFiddle演示
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
),
RecursiveTree2 AS
(
-- Anchor
SELECT *
FROM #tbl
WHERE Id = 2
UNION ALL
-- Recursive Member
SELECT Child.*
FROM
#tbl AS Child
JOIN RecursiveTree2 AS Parent ON Child.ParentId = Parent.id
)
select * from
(
SELECT * FROM RecursiveTree
union
SELECT * FROM RecursiveTree2
) t
order by id
上一篇: Use SQL CTE table to include path and all children
下一篇: SQL Server CTE select single tree branch structure upto the root