网站 › 『编程语言讨论求助区』 › postgre 类似 start with connect by prior

wuqingvika 发表于 2023-1-18 10:39

postgre 类似 start with connect by prior

本帖最后由 wuqingvika 于 2023-1-18 11:08 编辑

需求：要将oracle迁移到postgre里面用到了
SELECT
      so.*,
      connect_by_root ( so.org_id ) root_id
FROM
      ( SELECT * FROM sys_organization WHERE TYPE = 30 ) so START WITH sys_level = 2 CONNECT BY PRIOR so.org_id = so.sup_id

要转变成postgre的写法，递归求 当前是在根（=2层算起的）第几层


with recursive fg_tree as (
select so.*,
         so.org_id as root_id -- <<< this is the "root"
from sys_organization so
where TYPE = 30 and sys_level=2-- <<< this is the "start with" part
union all
select c.*,
         p.sup_id
from sys_organization c
    join fg_tree p onc.type=30 and p.org_id = c.sup_id -- <<< this is the "connect by" part
)

select *
from fg_tree;

查看完整版本: postgre 类似 start with connect by prior