How to get the full Hierarchy with SQL CTE

问题:

Hi I am trying to get the full hierarchy of my category. Here is my sample table

ID     PARENT_ID       NAME          DEPTH
------------------------------------------
1      NULL            A             1
2      NULL            B             1
3      NULL            C             1
4      1               D             2
5      4               E             3

The cte output should be this

ID     PARENT_ID       NAME
---------------------------
1      NULL            A             
2      NULL            B             
3      NULL            C             
4      1               D             
5      4               E             
5      1               E  

As you can see id:5 is parent of 4 and 1. How do i query the whole tree

回答1:

DECLARE @tmp TABLE(ID INT,ParentID INT,NAME VARCHAR(10),DEPTH INT)

INSERT INTO @tmp VALUES
(1      ,NULL            ,'A'             ,1 ),
(2      ,NULL            ,'B'             ,1 ),
(3      ,NULL            ,'C'             ,1 ),
(4      ,1               ,'D'             ,2 ),
(5      ,4               ,'E'            ,3 ),
(6      ,5               ,'F'            ,4 );

select * from @tmp

;WITH cte AS
(
    SELECT   A.ID
            ,A.ParentID
            ,A.NAME
            ,A.DEPTH
    FROM    @tmp A

    UNION ALL

    SELECT   A.ID
            ,B.ParentID
            ,A.NAME
            ,A.DEPTH 
    FROM    cte A 
    INNER JOIN @tmp B on A.ParentID = B.ID 
    WHERE   B.ParentID IS NOT NULL

)

SELECT * FROM cte

回答2:

In SQL Server 2008 CTE can be use to query recursively.

Example of CTE from MSDN

-- Sample Solution (Untested)

;With TableCTE(Id, Name, ParentId, Depth)
(
   Select ID,Name,ParentId, 1
     FROM MyTable

   Union All

   Select C.Id, C.Name, t.ParentId, c.Depth +1
     FROM @tmp t
    INNER JOIN TableCTE C on t.Id = c.ParentId
    -- Where t.ParentId IS Not Null
)

SELECT Id, Name, ParentId
FROM TableCTE

回答3:

;WITH cte AS
(
    SELECT   A.ID
            ,A.ParentID
            ,A.NAME
            ,A.DEPTH
    FROM    @tmp A
    WHERE   A.ParentID IS NULL

    UNION ALL

    SELECT   B.ID
            ,B.ParentID
            ,B.NAME
            ,B.DEPTH 
    FROM    cte A 
              INNER JOIN 
            @tmp B on B.ParentID = A.ID
    WHERE   B.ParentID IS NOT NULL

)

SELECT * FROM cte