无限级结构SQL查询所有的下级和所有的下级

1

2

3

4

5

6

7

8

9

/*查找Id为1所有的下级*/

WITH T

AS( 

    SELECT Id,PId,Name,0 L FROM User WHERE Id=1

    UNION ALL 

    SELECT U.Id,U.PId,U.Name,L+1   

    FROM User U INNER JOIN T ON U.PId=T.Id  

) 

SELECT * FROM T

1

2

3

4

5

6

7

8

9

/*查找Id为100所有的上级*/

WITH T

AS( 

    SELECT Id,PId,Name,0 L FROM User WHERE Id=100

    UNION ALL 

    SELECT U.Id,U.PId,U.Name,L+1   

    FROM User U INNER JOIN T ON U.Id=T.PId  

) 

SELECT * FROM T