开发者社区> 问答> 正文

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

Oracle的sql:
select t.org_id,

       t.org_name,
       t.org_level,
       connect_by_root(org_id)  org_up_id,
       connect_by_root(org_name)  org_up_name,
       connect_by_root(org_level)  org_level,
       connect_by_isleaf  leaf

from dm_org t
connect by prior t.org_id=decode(t.org_up_id , t.org_id , 0 , t.org_up_id);

请教各位大神,此段sql用PostgreSQL如何写?

展开
收起
土豆6006 2017-02-06 10:37:32 2997 0
1 条回答
写回答
取消 提交回答
  • 阿里巴巴-淘宝-新制造-高级技术专家,PostgreSQL、Clojure、FreeBSD等技术爱好者

    根据题目的描述,假设表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):名称

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

    org_idorg_up_idorg_levelorg_name
    111'A'
    212'B'
    312'C'
    423'D'
    533'E'
    654'F'

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

    org_idorg_nameorg_levelorg_up_idorg_up_nameorg_levelleaf
    1A11A10
    2B21A10
    4D31A11
    3C21A10
    5E31A10
    6F41A11
    2B22B20
    4D32B21
    3C23C20
    5E33C20
    6F43C21
    4D34D31
    5E35E30
    6F45E31
    6F46F41

    因为没有指定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:递归查询所有记录的子节点。
    2019-07-17 20:45:59
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践 立即下载
PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移 立即下载
Oracle云上最佳实践 立即下载

相关镜像