关闭：如何使用递归 SQL 查询提取森林的想法
我需要一个想法，如何在 PostgreSql 中使用递归 WITH .. RECURSIVE 查询来提取记录森林。数据结构大致看起来像
create table rule ( id int primary key, obj_id int, start_time timestamp )
我需要提取每个对象的所有规则链，并通过当第 i 个记录在功能上依赖于前一个记录时重新计算每个第 i 个记录的 start_time 。这就是我需要递归查询的方式。我有一个想法如何提取单个列表，它看起来像
with recursive rq (
select id, obj_id, start_time, id as prev from rule where id = :some_value
union all
select q.*
from (select id, obj_id, t, lead(r.id) over (order by start_time) as prev) q
join rq on rq.prev = q.id
)
并给出类似的序列
(root(A)) -> (rule1) -> (rule2) -> ...
，但我需要每个对象的列表：
(root(A)) -> (rule1) -> (rule2) -> ...
(root(B)) -> (rule1) -> (rule2) -> ...
...
(root(N)) -> (rule1) -> (rule2) -> ...
或：
object1|rule1
object1|rule2
...
object2|rule1
object2|rule2
...
在这里我完全陷入困境。我读过许多有关树和列表的示例，但它们通常使用单个根记录进行操作。有没有可能用多个根来实现这个技巧？我可能需要提取非递归部分中的整个记录集，然后将其与自身重新计算 start_time 字段结合起来？还有其他想法吗？
I need an idea, how to extract a forest of records using a recursive WITH .. RECURSIVE query in PostgreSql. The data structure is approximately looks like
create table rule ( id int primary key, obj_id int, start_time timestamp )
I need to extract all chains of rules for the every object, and recalculate the start_time for every i-th record by the way when i-th record functionally depends from the previous record. That's way I need a recursive query. I've got an idea how to extract a single list, it's looks like
with recursive rq (
select id, obj_id, start_time, id as prev from rule where id = :some_value
union all
select q.*
from (select id, obj_id, t, lead(r.id) over (order by start_time) as prev) q
join rq on rq.prev = q.id
)
and gives a sequence like
(root(A)) -> (rule1) -> (rule2) -> ...
but I need a a list for the every object:
(root(A)) -> (rule1) -> (rule2) -> ...
(root(B)) -> (rule1) -> (rule2) -> ...
...
(root(N)) -> (rule1) -> (rule2) -> ...
or:
object1|rule1
object1|rule2
...
object2|rule1
object2|rule2
...
and here I'm totally stuck. I've read a number of examples with trees and lists, but they usually operate with a single root record. Is there a possible way to do the trick with multiple roots? May be I need to extract the whole record set in the non-recursive part and then join it with itself recalculating the start_time field? Any other ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
为什么不首先选择所有根，然后使用递归选项来填充森林？
Why not first select all roots and then use the with recursive option to fill the forest?