查找多父层次结构的深度 - SQL

Question

我有一个包含两列的表：母亲 - 节点 该表构成了层次结构的基础。母亲指的是节点。 每个节点可以有多个母亲，每个母亲可以有多个孩子。这是通过多行来完成的。 如果 mother = NULL，则该节点是顶级节点。可以有多个顶级节点，一个节点既可以是顶级节点，也可以是另一个节点的子节点。 例如：

INSERT INTO MYTABLE VALUES(NULL, 2)
INSERT INTO MYTABLE VALUES(1, 2)

我现在正在构建一个程序，需要知道层次结构的最大深度。假设节点 E 是节点 D 的子节点，而节点 D 又是节点 C 的子节点。 节点 C 是顶级节点，也是节点 B 的子节点，节点 B 是节点 A 的子节点 节点A只是顶级节点。 如果我们说节点 A 的深度 = 0。那么在这种情况下，节点 E 的深度应该是 4。

有谁知道我如何构建一个可以为我找到这个深度的语句？ 它必须找到表中每个节点的最大深度，然后返回这些节点的最大值。

谢谢！

顺便说一句，使用 SQL Server 2008。

编辑：

感兴趣的只是表的绝对最大深度。不是单个节点的深度。

Answer 1

试试这个，它会找到层次结构的所有底层

declare @mytable table(id int, parent_id int)

INSERT INTO @MYTABLE VALUES(1, NULL) 
INSERT INTO @MYTABLE VALUES(2, 1) 
INSERT INTO @MYTABLE VALUES(3, 1) --*
INSERT INTO @MYTABLE VALUES(4, 2) 
INSERT INTO @MYTABLE VALUES(5, 4) --*

;with a as
(
    select 
       id, 
       parent_id, 
       1 lvl 
    from @mytable 
    where parent_id is null
    union all
    select 
        b.id, 
        b.parent_id, 
        lvl+1 
    from @mytable b 
        join a on a.id = b.parent_id
)
select 
    a.id, 
    a.parent_id, 
    a.lvl 
from a
    left join a b on a.id = b.parent_id
where b.id is null
option (maxrecursion 0)

Answer 2

好的。我找到了解决方案。

首先，我创建了一个带有两个额外列的临时表：

    CREATE TABLE #ParentChildTempTable
(
    Node NVARCHAR(50),
    Parent NVARCHAR(50),
    Depth TINYINT,
    Measure_Depth BIT
)

然后我将 true 顶级节点的深度设置为 0。False 顶级节点将 Measure_Depth 设置为 0。

    UPDATE #ParentChildTempTable SET Depth = 0 WHERE Parent IS NULL AND node not in 
(select Node from #ParentChildTempTable temp2 where temp2.Parent is not null)

UPDATE #ParentChildTempTable SET Measure_Depth = 0 WHERE Parent IS NULL AND node in 
(select Node from #ParentChildTempTable temp2 where temp2.Parent is not null)

然后我从顶层向下循环直到每个节点都有一个深度（预计假顶级别）

    WHILE EXISTS (SELECT * FROM #ParentChildTempTable WHERE Depth IS NULL AND Measure_Depth IS NULL) 
    UPDATE T SET T.Depth = P.Depth + 1
    FROM #ParentChildTempTable AS T 
    INNER JOIN #ParentChildTempTable AS P ON (T.Parent=P.Node) 
    WHERE P.Depth>=0 
    AND T.Depth IS NULL

瞧！找到最大深度：

DECLARE @MaxDepth INT = (SELECT MAX(Depth) FROM #ParentChildTempTable)