此问题基于以下问题,但有附加要求:PostgreSQL: How to find the last descendant in a linear "ancestor-descendant" relationship
基本上,我需要的是一个Postgre SQL语句,它在符合附加条件的线性“祖先-后代”关系中找到最后一个后代。
例子:
这里是“关系表”的内容:
id | id_ancestor | id_entry | bool_flag
---------------------------------------
1 | null | a | false
2 | 1 | a | false
3 | 2 | a | true
4 | 3 | a | false
5 | null | b | true
6 | null | c | false
7 | 6 | c | false
特定层次结构中的每个记录都具有相同的“id_entry”
本例中有3种不同的“祖先-后代”关系:
1. 1 <- 2 <- 3 <- 4
2. 5
3. 6 <- 7
问题PostgreSQL: How to find the last descendant in a linear "ancestor-descendant" relationship显示了如何找到每种关系的最后一条记录。在上面的例子中:
1. 4
2. 5
3. 7
所以,这次我需要的是“id_entry”的最后一个后代,它的“bool_flag”设置为true。在上面的例子中:
1. 3
2. 5
3. <empty result>
有人知道解决办法吗?
提前谢谢:)
Q风暴
最佳答案
用边列表表示的图、树、链等通常是递归公共表表达式(即WITH RECURSIVE
查询)的好用法。
类似于:
WITH RECURSIVE walk(id, id_ancestor, id_entry, bool_flag, id_root, generation) AS (
SELECT id, id_ancestor, id_entry, bool_flag, id, 0
FROM RELATIONSHIP_TABLE
WHERE id_ancestor IS NULL
UNION ALL
SELECT x.id, x.id_ancestor, x.id_entry, x.bool_flag, walk.id_root, walk.generation + 1
FROM RELATIONSHIP_TABLE x INNER JOIN walk ON x.id_ancestor = walk.id
)
SELECT
id_entry, id_root, id
FROM (
SELECT
id, id_entry, bool_flag, id_root, generation,
max(CASE WHEN bool_flag THEN generation END ) OVER w as max_enabled_generation
FROM walk
WINDOW w AS (PARTITION BY id_root ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
) x
WHERE generation = max_enabled_generation;
... 尽管这让人觉得应该有更好的方法来做到这一点,而不是追踪我们每一条道路上走过了多少代人。
如果
id_entry
对于树的所有成员都是通用的,则可以避免跟踪id_root
。您应该在UNIQUE
上创建(id_entry, id)
约束,在FOREIGN KEY (id_entry, id_ancestor) REFERENCES (id_entry, id)
上创建外键约束,以确保顺序一致,然后使用:WITH RECURSIVE walk(id, id_ancestor, id_entry, bool_flag, generation) AS (
SELECT id, id_ancestor, id_entry, bool_flag, 0
FROM RELATIONSHIP_TABLE
WHERE id_ancestor IS NULL
UNION ALL
SELECT x.id, x.id_ancestor, x.id_entry, x.bool_flag, walk.generation + 1
FROM RELATIONSHIP_TABLE x INNER JOIN walk ON x.id_ancestor = walk.id
)
SELECT
id_entry, id
FROM (
SELECT
id, id_entry, bool_flag, generation,
max(CASE WHEN bool_flag THEN generation END ) OVER w as max_enabled_generation
FROM walk
WINDOW w AS (PARTITION BY id_entry ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
) x
WHERE generation = max_enabled_generation;
因为这将为您提供一个与根父级匹配的最终子代表,所以您现在只需使用常规
WHERE
子句进行筛选,只需追加AND bool_flag
。如果您希望排除一路上任何一点bool_flag
设置为false的链,则可以在WHERE bool_value
查询的连接中添加RECURSIVE
。SQLFiddle示例:http://sqlfiddle.com/#!12/92a64/3
关于postgresql - 如何以线性“祖先后代”关系找到最后一个后代(与其他条件匹配),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17229037/