postgre 类似 start with connect by prior

wuqingvika

需求：要将oracle迁移到postgre  里面用到了

[SQL] 纯文本查看 复制代码

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层算起的）第几层  

[SQL] 纯文本查看 复制代码

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 on  c.type=30 and p.org_id = c.sup_id -- <<< this is the "connect by" part
) 

select *
from fg_tree;