sql - 确保递归查询的唯一性

我有一个非常基本的父子/树层次结构和一个递归查询，它还可以添加depth并完美地加载所有内容…几乎。当我尝试加载多个节点（其中一个节点是另一个节点的子节点）时，会得到重复的行（因为depth会得到更新，并且行不再相同）。
我确实读过documentation，我没有使用UNION ALL，我试过文档中的NOT cycle技巧，我知道ltree数据类型，但我不能使用它。这是另一种情况，请考虑以下树：

5
├─9
│ └─15
├─10
│ └─16
└─11
  └─17

以及查询：

WITH RECURSIVE "CTE" AS
(
    SELECT "id", 0 AS "depth"
    FROM "Node" WHERE "id" IN (5, 9, 15)
    UNION
    SELECT "Node"."id", "CTE"."depth" + 1
    FROM "CTE" JOIN "Node" ON "Node"."parentId" = "CTE"."id"
)
SELECT *
FROM "CTE"
ORDER BY "id";

结果是：

id  depth
5   0
9   0
9   1
10  1
11  1
15  0
15  1
15  2
16  2
17  2

而不是期望的结果：

id  depth
5   0
9   0
10  1
11  1
15  0
16  2
17  2

使用WHERE "id" = 5运行同一个查询会产生这样的结果（注意，深度有多不同，因为选择是从根开始的）：

id  depth
5   0
9   1
10  1
11  1
15  2
16  2
17  2

解决方法是将join修改为：

FROM "CTE" JOIN "Node" ON
    "Node"."parentId" = "CTE"."id" AND
    "Node"."id" NOT IN (SELECT "id" FROM "CTE")

但是Postgres不允许从子查询引用“CTE”。我想知道这个问题有没有正确的解决方法？
顺便说一句，我确实想出了一个可行的解决方案，我在几个不同的场景中尝试过，但我不能百分之百肯定它在所有情况下都能奏效。它基本上消除了最初选择的值，确保迭代不会“输入”它们。这种方法有什么缺点吗？

WITH RECURSIVE "CTE" AS
(
    SELECT "id", 0 AS "depth"
    FROM "Node" WHERE "id" IN (5, 9, 15)
    UNION
    SELECT "Node"."id", "CTE"."depth" + 1
    FROM "CTE" JOIN "Node" ON
        "Node"."parentId" = "CTE"."id"
        AND NOT IN (5, 9, 15)
)
SELECT *
FROM "CTE"
ORDER BY "id";

最佳答案

-- Data
CREATE TABLE node
        ( id integer NOT NULL PRIMARY KEY
        , parentid integer REFERENCES node(id)
        );

INSERT INTO node(id,parentid) VALUES
(5, NULL)
, (9,5), (10,5), (11,5)
, (15,9), (16,10), (17,11)
        ;

-- query
WITH RECURSIVE tree AS (
    SELECT id, 0 AS depth
    FROM node WHERE id IN (5, 9, 15)
    UNION
    SELECT node.id, tree.depth + 1
    FROM tree JOIN node ON node.parentid = tree.id
    )
SELECT *
FROM tree tr
WHERE NOT EXISTS ( -- trivial way to suppress duplicates with longer path
        SELECT *
        FROM tree nx
        WHERE nx.id = tr.id
        AND nx.depth < tr.depth
        )
ORDER BY id
        ;

更新：这看起来成本更低。对于给定的数据是正确的（但在一般情况下不是IIUC）：

WITH RECURSIVE tree AS (
    SELECT id, 0 AS depth
    FROM node WHERE id IN (5, 9, 15)
    UNION
    SELECT node.id, tree.depth + 1
    FROM tree JOIN node ON node.parentid = tree.id
    WHERE node.id NOT IN (5, 9, 15)
    )
SELECT *
FROM tree tr
ORDER BY id
        ;

关于sql - 确保递归查询的唯一性，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29256402/