使用SQL CTE表来包含路径和所有子级

Question

我有一个层次结构树形表格，如下：

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

问题：如何将这两个命令合并为一个命令？

- AaA

---

+1 针对提供良好的解释、DDL、DML 和测试数据的贡献。 - Ivan Golović

Answer 1

只需使用这两个SELECT语句的UNION操作即可。

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

- valex