gpt4 book ai didi

sql - 使用 SQL CTE 表包含路径和所有子项

转载 作者:行者123 更新时间:2023-12-02 16:38:10 24 4
gpt4 key购买 nike

我有以下层次树表

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

问题:如何将这两个命令合并为一个?

最佳答案

只需使用这两个选择的 UNION

SQLFiddle demo

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

关于sql - 使用 SQL CTE 表包含路径和所有子项,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14849410/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com