从层次结构中删除项目

马蒂亚斯

我有一些这样的树结构

树中的某些节点将被禁用。禁用节点的子节点需要连接到禁用节点的父节点。如果那个被禁用，然后到以下父级等等。

在这张图片中，节点 2 被禁用，节点 4 和 5 连接到节点 2 的父节点，即节点 1

这将是结果

一个更复杂的案例 ID，其中禁用节点的父节点也被禁用

在这个示例中，我得到了一些测试数据的所有子节点，但我不知道是否可以删除禁用的节点但保持子节点连接到下一个可能的父节点

CREATE TABLE #Node
(
     Id INT, 
     ParentID INT, 
     Name NVARCHAR(20),   
     skipNode BIT
);

INSERT INTO #Node 
VALUES (1, NULL, 'node-1', 0),
       (2, 1, 'node-2', 1),
       (3, 1, 'node-3', 0),
       (4, 2, 'node-4', 0),
       (5, 2, 'node-5', 0),
       (6, 3, 'node-6', 0),
       (7, 4, 'node-6', 0),
       (8, 4, 'node-6', 0);

WITH RCTE AS
(
    SELECT 
        anchor.Id AS ItemId, 
        skipNode,
        anchor.ParentId AS ItemParentId, 
        1 AS Lvl, 
        anchor.[Name],
        CAST(name AS VARCHAR(1000)) AS NodePath
    FROM
        #Node anchor 
    WHERE 
        anchor.[Id] = 1

    UNION ALL

    SELECT 
        nextDepth.Id AS ItemId, 
        nextDepth.skipNode, 
        nextDepth.ParentId AS ItemParentId, 
        Lvl+1 AS Lvl, 
        nextDepth.[Name],
        CAST((rec.NodePath + '/' + nextDepth.[Name]) AS VARCHAR(1000)) AS NodePath
    FROM 
        #Node nextDepth
    INNER JOIN 
        RCTE rec ON nextDepth.ParentId = rec.ItemId
)
SELECT ItemId, skipNode , ItemParentId, [Name], NodePath
FROM RCTE AS hierarchy

DROP TABLE #Node

禁用节点 2 的预期结果将是

ItemId      skipNode ItemParentId Name   
----------- -------- ------------ -------
1           0        NULL         node-1 
3           0        1            node-3 
6           0        3            node-6 
4           0        1            node-4 
5           0        1            node-5 
7           0        4            node-6 
8           0        4            node-6

格雷格·洛

这个例子应该让你开始：

USE tempdb;
GO

DROP FUNCTION IF EXISTS dbo.GetParentNode;
GO

CREATE FUNCTION dbo.GetParentNode
( 
    @NodePath varchar(max)
)
RETURNS int
AS 
BEGIN
    /*
        SELECT dbo.GetParentNode('12/13/14');
        SELECT dbo.GetParentNode('12/14');
    */

    DECLARE @ReturnValue int;
    DECLARE @StringToProcess varchar(max) = REVERSE(@NodePath);
    DECLARE @DelimiterLocation int;

    SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

    IF @DelimiterLocation > 0
    BEGIN
        SET @StringToProcess = SUBSTRING(@StringToProcess, @DelimiterLocation + 1, LEN(@StringToProcess));
        SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

        IF @DelimiterLocation = 0
        BEGIN
            SET @ReturnValue = CAST(REVERSE(@StringToProcess) AS int);
        END ELSE BEGIN
            SET @ReturnValue = CAST(REVERSE(LEFT(@StringToProcess, @DelimiterLocation - 1)) AS int);
        END;
    END;

    RETURN @ReturnValue;   
END;
GO

DROP TABLE IF EXISTS dbo.Nodes;
GO

CREATE TABLE dbo.Nodes
(
     NodeID int, 
     ParentNodeID int, 
     NodeName nvarchar(20),   
     IsDisabled bit
);

INSERT dbo.Nodes 
(
    NodeID, ParentNodeID, NodeName, IsDisabled
)
VALUES (1, NULL, 'node-1', 0),
       (2, 1, 'node-2', 1),
       (3, 1, 'node-3', 0),
       (4, 2, 'node-4', 0),
       (5, 2, 'node-5', 0),
       (6, 3, 'node-6', 0),
       (7, 4, 'node-6', 0),
       (8, 4, 'node-6', 0);

WITH AllNodes AS
(
    SELECT toplevel.NodeID, 
           toplevel.IsDisabled,
           toplevel.ParentNodeID, 
           1 AS NodeLevel, 
           toplevel.NodeName, 
           CAST(toplevel.NodeID AS varchar(max)) AS NodePath
    FROM dbo.Nodes AS toplevel 
    WHERE toplevel.NodeID = 1

    UNION ALL

    SELECT 
        n.NodeID, 
        n.IsDisabled, 
        n.ParentNodeID, 
        an.NodeLevel + 1, 
        n.NodeName,
        an.NodePath + CASE WHEN n.IsDisabled = 0 
                           THEN '/' + CAST(n.NodeID AS varchar(max))
                           ELSE ''
                      END
    FROM dbo.Nodes AS n
    INNER JOIN AllNodes AS an 
    ON an.NodeID = n.ParentNodeID
)
SELECT an.NodeID, an.IsDisabled, an.NodeName,
       an.ParentNodeID, an.NodeLevel, an.NodePath,
       dbo.GetParentNode(an.NodePath) AS TrueParentNodeID
FROM AllNodes AS an
WHERE an.IsDisabled = 0;

DROP TABLE dbo.Nodes;
GO

只需执行整个操作，看看它是否正确。希望有帮助。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。