sql - Hierarchical SQL Queries : Best SQL query to obtain the whole branch of a tree from a [nodeid, parentid] pairs table 给定结束节点id

Question

有什么方法可以在 SQL 中发送递归查询吗？

给定结束节点 ID，我需要按级别排序直到根节点(具有 parentid = NULL)的所有行。例如。如果我有类似的东西:

nodeid | parentid
a      | NULL    
b      | a       
c      | b       

在查询 end_node_id = c 后，我会得到如下内容:

nodeid | parentid | depth
a      | NULL     | 0
b      | a        | 1
c      | b        | 2

(除了深度，我还可以使用到给定端节点的距离)

我能想到的唯一(也是显而易见的)方法是每行执行一个查询，直到到达父节点。

有没有更有效的方法？

Answer 1

如果您使用的是 mssql 2005+，您可以这样做:

测试数据:

DECLARE @tbl TABLE(nodeId VARCHAR(10),parentid VARCHAR(10))

INSERT INTO @tbl
VALUES ('a',null),('b','a'),('c','b')

查询

;WITH CTE
AS
(
    SELECT
        tbl.nodeId,
        tbl.parentid,
        0 AS Depth
    FROM
        @tbl as tbl
    WHERE
        tbl.parentid IS NULL
    UNION ALL
    SELECT
        tbl.nodeId,
        tbl.parentid,
        CTE.Depth+1 AS Depth
    FROM
        @tbl AS tbl
        JOIN CTE
            ON tbl.parentid=CTE.nodeId
)
SELECT
    *
FROM
    CTE