请教PostgreSQL与ORACLE兼容的问题，递归查询sql

根据题目的描述，假设表dm_org，有四个字段：

• org_id int：节点唯一编号。
• org_up_id int：父节点编号，根据decode(t.org_up_id , t.org_id , 0 , t.org_up_id)，推测根节点的org_up_id等于org_id。
• org_level int：层级？
• org_name varchar(64)：名称

假设表中包含以下六行记录：

利用题主的SQL查询结果如下：

因为没有指定start with语句，从功能上来说，是递归查询dm_org表所有记录的子节点。与之对应的PostgreSQL的查询语句是：

with recursive leafs as (
  select
   distinct
    parents.org_id as leaf_id
  from
    dm_org as parents
  left join
    dm_org as children
  on
    parents.org_id = children.org_up_id
  where
    children.org_id is null
), trees as (
  select
    org_id,
    org_name,
    org_level,
    org_id as org_up_id,
    org_name as org_up_name,
    org_level as org_up_level
  from
    dm_org

  union all

  select
    dm_org.org_id,
    dm_org.org_name,
    dm_org.org_level,
    trees.org_up_id,
    trees.org_up_name,
    trees.org_up_level
  from
    dm_org
  inner join
    trees
  on
    dm_org.org_up_id = trees.org_id
    and dm_org.org_id != trees.org_id
)
select
  trees.*,
  leafs.leaf_id is not null as leaf
from
  trees
left join
  leafs
on
  trees.org_id = leafs.leaf_id
order by
  org_up_id,
  org_id;

定义了两个公共表表达式，其中：

• leafs：获得所有叶子节点。因为PostgreSQL没有提供connect_by_isleaf这样的关键字。
• trees：递归查询所有记录的子节点。