sql-server - 在数据库表中递归搜索，包括每行的一个到 N 个父项

Question

我有一张 table ，看起来像那样

 id   parent_id
 1    null
 2    1
 3    2
 4    1
 5    3  

对于表中的每一行，我想检索有关其父代的信息(从第一代到第 N 代)。这意味着，如果 id = 3 的行有 parent_id = 2 并且 id = 2 有 parent_id = 1，那么 3 也属于 2 和 1。

我想要得到的结果:

id    multi_level_parent_id
 1    null
 2    1
 3    2
 3    1
 4    1
 5    3
 5    2
 5    1

我假设，我必须使用递归 select。我写了SQL代码，但它只返回第一代的信息

WITH Rec AS
(
    SELECT *
    FROM MyTable t
    UNION ALL
    SELECT *
    FROM MyTable t
    INNER JOIN Rec r ON t.id = r.parent_id
)

SELECT *
FROM Rec

有人知道如何检索我需要的信息吗？

Answer 1

这就是你想要的:

WITH Rec
     AS (
     SELECT id,
            id AS parent_id,
            0 AS steps
     FROM MyTable  t
     UNION ALL
     SELECT r.id,
            t.parent_id,
            r.steps + 1 AS steps
     FROM MyTable  t
          INNER JOIN Rec r ON t.id = r.parent_id)
     SELECT id,
            parent_id
     FROM MyTable 
     WHERE parent_id IS NULL
     UNION ALL
     SELECT id,
            parent_id
     FROM Rec
     WHERE parent_id <> id
     ORDER BY id;

结果: