[教学]SQL Server从子节点取出所有的父节点，select all parent node

[教学]SQL Server从子节点取出所有的父节点，select all parent node from child node

Sample 1 Order By Depth

1. DECLARE @ItemCategory TABLE
   
2. (
   
3.     ChildId INT NOT NULL,
   
4.     Name varchar(50) NOT NULL,
   
5.     ParentId int NULL
   
6. )
   
7. 
   
8. INSERT INTO @ItemCategory SELECT 1, 'Toys', null
   
9. INSERT INTO @ItemCategory SELECT 2, 'Computers', null
   
10. INSERT INTO @ItemCategory SELECT 3, 'Consoles', 2
    
11. INSERT INTO @ItemCategory SELECT 4, 'PlayStation 3', 3
    
12. INSERT INTO @ItemCategory SELECT 5, 'Xbox 360', 2
    
13. INSERT INTO @ItemCategory SELECT 6, 'Games', 1
    
14. INSERT INTO @ItemCategory SELECT 7, 'Puzzles', 6
    
15. INSERT INTO @ItemCategory SELECT 8, 'Mens Wear', null
    
16. INSERT INTO @ItemCategory SELECT 9, 'Mens Clothing', 8
    
17. INSERT INTO @ItemCategory SELECT 10, 'Jackets', 9
    
18. INSERT INTO @ItemCategory SELECT 11, 'Rain Coats', 10
    
19. 
    
20. DECLARE @ChildID INT = 11
    
21. 
    
22. ;WITH c
    
23. AS
    
24. (
    
25.     SELECT ChildId, ParentId, Name, 1 AS Depth
    
26.     FROM @ItemCategory
    
27.     WHERE ChildId = @ChildID
    
28. 
    
29.     UNION ALL
    
30. 
    
31.     SELECT t.ChildId, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
    
32.     FROM @ItemCategory T  
    
33.     INNER JOIN c ON t.ChildId = c.ParentId
    
34. )
    
35. SELECT *
    
36. FROM c

复制代码

Sample 2 Order By Depth DESC

1. DECLARE @ItemCategory TABLE
   
2. (
   
3.     ChildId INT NOT NULL,
   
4.     Name varchar(50) NOT NULL,
   
5.     ParentId int NULL
   
6. )
   
7. 
   
8. INSERT INTO @ItemCategory SELECT 1, 'Toys', null
   
9. INSERT INTO @ItemCategory SELECT 2, 'Computers', null
   
10. INSERT INTO @ItemCategory SELECT 3, 'Consoles', 2
    
11. INSERT INTO @ItemCategory SELECT 4, 'PlayStation 3', 3
    
12. INSERT INTO @ItemCategory SELECT 5, 'Xbox 360', 2
    
13. INSERT INTO @ItemCategory SELECT 6, 'Games', 1
    
14. INSERT INTO @ItemCategory SELECT 7, 'Puzzles', 6
    
15. INSERT INTO @ItemCategory SELECT 8, 'Mens Wear', null
    
16. INSERT INTO @ItemCategory SELECT 9, 'Mens Clothing', 8
    
17. INSERT INTO @ItemCategory SELECT 10, 'Jackets', 9
    
18. INSERT INTO @ItemCategory SELECT 11, 'Rain Coats', 10
    
19. 
    
20. DECLARE @ChildID INT = 11
    
21. 
    
22. ;WITH c
    
23. AS
    
24. (
    
25.     SELECT ChildID, ParentId, Name, 1 AS Depth
    
26.     FROM @ItemCategory
    
27.     WHERE ChildID = @ChildID
    
28. 
    
29.     UNION ALL
    
30. 
    
31.     SELECT t.ChildID, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
    
32.     FROM @ItemCategory T  
    
33.     INNER JOIN c ON t.ChildID = c.ParentId
    
34. )
    
35. SELECT ChildID,
    
36.        ParentID,
    
37.        Name,
    
38.        MAX(Depth) OVER() - Depth + 1 AS InverseDepth
    
39. FROM c

复制代码