postgres中的connect_by_root等效项 [英] connect_by_root equivalent in postgres
本文介绍了postgres中的connect_by_root等效项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在postgres中隐蔽oracle的connect_by_root查询. 例如:这是一个oracle查询.
How I can covert connect_by_root query of oracle in postgres. ex: This is an oracle query.
select fg_id, connect_by_root fg_id as fg_classifier_id
from fg
start with parent_fg_id is null
connect by prior fg_id = parent_fg_id
推荐答案
您将使用一个递归公用表表达式,该表达式仅通过递归级别携带"根:
You would use a recursive common table expression that simply "carries" the root through the recursion levels:
with recursive fg_tree as (
select fg_id,
fg_id as fg_clasifier_id -- <<< this is the "root"
from fg
where parent_fg_id is null -- <<< this is the "start with" part
union all
select c.fg_id,
p.fg_clasifier_id
from fg c
join fg_tree p on p.fg_id = c.parent_fg_id -- <<< this is the "connect by" part
)
select *
from fg_tree;
手册中有关递归公用表表达式的更多详细信息: http://www.postgresql. org/docs/current/static/queries-with.html
More details on recursive common table expressions in the manual: http://www.postgresql.org/docs/current/static/queries-with.html
这篇关于postgres中的connect_by_root等效项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文