Postgres中的NOCYCLE [英] NOCYCLE in Postgres
本文介绍了Postgres中的NOCYCLE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有NOCYCLE
子句的Oracle查询,我必须将其转换为Postgres:
I have a Oracle query with a NOCYCLE
clause which I have to translate to Postgres:
SELECT FG_ID,CONNECT_BY_ROOT FG_ID as Parent_ID
FROM FG t
START WITH t.Parent_filter_group_id is null
CONNECT BY NOCYCLE PRIOR t.FILTER_GROUP_ID = t.PARENT_FILTER_GROUP_ID
我已经在问答的帮助下将其转换为 等同于postgres的connect_by_root
I have converted this one with the help of the question and answer in connect_by_root equivalent in postgres
为
with recursive fg_tree as (
select FG_ID,
FG_ID as fg
from FG
where Parent_filter_group_id is null
union all
select c.FG_ID,
p.fg
from FG c join fg_tree p on p.FG_ID = PARENT_FILTER_GROUP_ID
)
select * from fg_tree
order by FG_ID
但在此没有NOCYCLE
的子句,如果父级也是子级之一,则此查询将返回错误.
but in this there is no clause for NOCYCLE
if the parent is also one of the children then this query will return error.
推荐答案
您可以收集每个级别的ID,然后在路径中不包含当前" ID的条件下加入:
You can collect the IDs for each level and then join on the condition that the "current" id is not contained in the path:
with recursive fg_tree as (
select FG_ID,
FG_ID as fg,
array[fg_id] as path
from FG
where Parent_filter_group_id is null
union all
select c.FG_ID,
p.fg,
p.fg||c.fg_id
from FG c
join fg_tree p on p.FG_ID and c.fg_id <> ALL (p.path)
)
select fg_id, fg
from fg_tree
order by filter_group_id
这篇关于Postgres中的NOCYCLE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文