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

资讯王 发表于 2012-7-4 20:24:33

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

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

Sample 1 Order By Depth DECLARE @ItemCategory TABLE
(
    ChildId INT NOT NULL,
    Name varchar(50) NOT NULL,
    ParentId int NULL
)

INSERT INTO @ItemCategory SELECT 1, 'Toys', null
INSERT INTO @ItemCategory SELECT 2, 'Computers', null
INSERT INTO @ItemCategory SELECT 3, 'Consoles', 2
INSERT INTO @ItemCategory SELECT 4, 'PlayStation 3', 3
INSERT INTO @ItemCategory SELECT 5, 'Xbox 360', 2
INSERT INTO @ItemCategory SELECT 6, 'Games', 1
INSERT INTO @ItemCategory SELECT 7, 'Puzzles', 6
INSERT INTO @ItemCategory SELECT 8, 'Mens Wear', null
INSERT INTO @ItemCategory SELECT 9, 'Mens Clothing', 8
INSERT INTO @ItemCategory SELECT 10, 'Jackets', 9
INSERT INTO @ItemCategory SELECT 11, 'Rain Coats', 10

DECLARE @ChildID INT = 11

;WITH c
AS
(
    SELECT ChildId, ParentId, Name, 1 AS Depth
    FROM @ItemCategory
    WHERE ChildId = @ChildID

    UNION ALL

    SELECT t.ChildId, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
    FROM @ItemCategory T
    INNER JOIN c ON t.ChildId = c.ParentId
)
SELECT *
FROM cSample 2 Order By Depth DESC DECLARE @ItemCategory TABLE
(
    ChildId INT NOT NULL,
    Name varchar(50) NOT NULL,
    ParentId int NULL
)

INSERT INTO @ItemCategory SELECT 1, 'Toys', null
INSERT INTO @ItemCategory SELECT 2, 'Computers', null
INSERT INTO @ItemCategory SELECT 3, 'Consoles', 2
INSERT INTO @ItemCategory SELECT 4, 'PlayStation 3', 3
INSERT INTO @ItemCategory SELECT 5, 'Xbox 360', 2
INSERT INTO @ItemCategory SELECT 6, 'Games', 1
INSERT INTO @ItemCategory SELECT 7, 'Puzzles', 6
INSERT INTO @ItemCategory SELECT 8, 'Mens Wear', null
INSERT INTO @ItemCategory SELECT 9, 'Mens Clothing', 8
INSERT INTO @ItemCategory SELECT 10, 'Jackets', 9
INSERT INTO @ItemCategory SELECT 11, 'Rain Coats', 10

DECLARE @ChildID INT = 11

;WITH c
AS
(
    SELECT ChildID, ParentId, Name, 1 AS Depth
    FROM @ItemCategory
    WHERE ChildID = @ChildID

    UNION ALL

    SELECT t.ChildID, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
    FROM @ItemCategory T
    INNER JOIN c ON t.ChildID = c.ParentId
)
SELECT ChildID,
       ParentID,
       Name,
       MAX(Depth) OVER() - Depth + 1 AS InverseDepth
FROM c

查看完整版本: [教学]SQL Server从子节点取出所有的父节点，select all parent node