Use SQL CTE table to include path and all children

I have following hierarchical tree table

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

Which maps to following tree

0
+- 1
   +- 2
      +- 4
      +- 5
   +- 3
      +- 6
      +- 7

Using CTE Recursive table, how can I get the path and also all children of the selected node. For example having 2 as input, how can I get following data (ordered if possible)

Id, ParentID
-------
 0, NULL 
 1, 0
 2, 1
 4, 2
 5, 2

I know I can traverse up in the tree (get path) with following statement

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

And with following statement, traverse down in the tree (get all children)

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

Question: How to combine these two commands into one?

---

Just use UNION of these two selects

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

链接地址: http://www.djcxy.com/p/60224.html

上一篇: 如何修剪分层数据结构中的死分支

下一篇: 使用SQL CTE表来包含路径和所有子项