如何在使用RECURSIVE选择后代的Postgres查询中打印深度级别？(How does one print depth

如何在使用RECURSIVE选择后代的Postgres查询中打印深度级别？(How does one print depth-level in a Postgres query that uses RECURSIVE to select descendants?)

我有一个表persons ，其中包含parent_id的列，它引用同一个表中的另一行。 假设这是逻辑层次结构：

P1 P2 P3 P4 P5 P6 P7 P8 P9 P10

我编写了一个查询，打印给定节点的所有父节点，以及节点上方的高度，它似乎工作正常：

WITH RECURSIVE ancestors AS ( SELECT id, parent_id FROM persons WHERE id = 8 UNION SELECT p.id, p.parent_id FROM persons p INNER JOIN ancestors ON p.id = ancestors.parent_id ) SELECT persons.id, persons.name, ROW_NUMBER() over () as height FROM ancestors INNER JOIN persons ON ancestors.id = persons.id WHERE persons.id <> 8

结果：

id | name | height -------+-------------+--------- 3 | P3 | 1 1 | P1 | 2 (2 rows)

我现在想写一个类似打印所有后代的查询，以及深度。 这是迄今为止的查询（与上面相同，在UNION连接中交换了id和parent_id ）：

WITH RECURSIVE descendants AS ( SELECT id, parent_id FROM persons WHERE id = 1 UNION SELECT p.id, p.parent_id FROM persons p INNER JOIN descendants ON p.parent_id = descendants.id ) SELECT persons.id, persons.name, ROW_NUMBER() over () as depth FROM descendants INNER JOIN persons ON descendants.id = persons.id WHERE persons.id <> 1

这给出了以下结果：

id | name | depth -------+-------------+--------- 2 | P2 | 1 3 | P3 | 2 4 | P4 | 3 5 | P5 | 4 6 | P6 | 5 7 | P7 | 6 8 | P8 | 7 9 | P9 | 8 10 | P10 | 9 (9 rows)

显然，深度是错误的。 ROW_NUMBER()没有做我想要的。 我该怎么做？

我已经考虑过在查询本身的递归部分中使用一个计数器，它在每次运行时都会递增，但我不确定是否有办法实现这一点。

I have a table persons that contains a column for parent_id, which refers to another row in the same table. Assume this is the logical hierarchy:

P1 P2 P3 P4 P5 P6 P7 P8 P9 P10

I have written a query that prints all parents of a given node, along with the height above the node, and it seems to work fine:

WITH RECURSIVE ancestors AS ( SELECT id, parent_id FROM persons WHERE id = 8 UNION SELECT p.id, p.parent_id FROM persons p INNER JOIN ancestors ON p.id = ancestors.parent_id ) SELECT persons.id, persons.name, ROW_NUMBER() over () as height FROM ancestors INNER JOIN persons ON ancestors.id = persons.id WHERE persons.id <> 8

Result:

id | name | height -------+-------------+--------- 3 | P3 | 1 1 | P1 | 2 (2 rows)

I now want to write a query that similarly prints all descendants, along with depth. Here's the query so far (same as above with id and parent_id swapped in the UNION join):

WITH RECURSIVE descendants AS ( SELECT id, parent_id FROM persons WHERE id = 1 UNION SELECT p.id, p.parent_id FROM persons p INNER JOIN descendants ON p.parent_id = descendants.id ) SELECT persons.id, persons.name, ROW_NUMBER() over () as depth FROM descendants INNER JOIN persons ON descendants.id = persons.id WHERE persons.id <> 1

This gives the following result:

id | name | depth -------+-------------+--------- 2 | P2 | 1 3 | P3 | 2 4 | P4 | 3 5 | P5 | 4 6 | P6 | 5 7 | P7 | 6 8 | P8 | 7 9 | P9 | 8 10 | P10 | 9 (9 rows)

Clearly, the depth is all wrong. ROW_NUMBER() isn't doing what I want. How do I go about this?

I've thought about using a counter within the recursive part of the query itself, which increments every time it is run, but I'm not sure if there's a way to achieve that.

最满意答案

使用其他列depth ：

WITH RECURSIVE descendants AS ( SELECT id, parent_id, 0 depth FROM persons WHERE id = 1 UNION SELECT p.id, p.parent_id, d.depth+ 1 FROM persons p INNER JOIN descendants d ON p.parent_id = d.id ) SELECT p.id, p.name, depth FROM descendants d INNER JOIN persons p ON d.id = p.id WHERE p.id <> 1; id | name | depth ----+------+------- 2 | P2 | 1 3 | P3 | 1 4 | P4 | 1 5 | P5 | 2 6 | P6 | 2 7 | P7 | 2 8 | P8 | 2 9 | P9 | 2 10 | P10 | 2 (9 rows)

Use additional column depth:

WITH RECURSIVE descendants AS ( SELECT id, parent_id, 0 depth FROM persons WHERE id = 1 UNION SELECT p.id, p.parent_id, d.depth+ 1 FROM persons p INNER JOIN descendants d ON p.parent_id = d.id ) SELECT p.id, p.name, depth FROM descendants d INNER JOIN persons p ON d.id = p.id WHERE p.id <> 1; id | name | depth ----+------+------- 2 | P2 | 1 3 | P3 | 1 4 | P4 | 1 5 | P5 | 2 6 | P6 | 2 7 | P7 | 2 8 | P8 | 2 9 | P9 | 2 10 | P10 | 2 (9 rows)