我有一个表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 P10I 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 <> 8Result:
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 <> 1This 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)更多推荐
发布评论