oracle之connect by层级查询树形数据

文章目录

• 
  
• ​​场景​​
• ​​语法​​
• ​​实践过程​​
• • • 
      
  • ​​创建`sys_org`表​​
  • ​​模拟数据示例​​
  • ​​向下查找​​
  • ​​向上查找​​
  • ​​将同一层级组织名称放在一行​​
  • ​​`listagg()` 行转列函数​​
  • ​​参考博文​​
    • 
      
• 
  

场景

​​connect by​​ 用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。

语法

select  *  from  表名  start  with 条件1  connnect by 条件2  where  条件3

语法说明

• • 
    
• ​​start with​​​: ​​条件1​​表示递归从哪里开始
• ​​connect by​​​: ​​条件2​​​ 表示递归时前后两条数据是以​​条件2​​来建立联系的
• ​​prior​​: 的位置决定了递归时的具体关系或者说是决定了查询时的检索顺序
• ​​level​​​:关键字表示层次,根节点的层号为​​1​​
  • 
    

实践过程

创建​​sys_org​​表

create table sys_org
(
  org_id int
    constraint SYS_ORG_PK
      primary key,
  org_name varchar2(255),
  parent_id int,
  org_code int,
  org_path int,
  org_path_name varchar2(255),
  order_id int
)
/

comment on table sys_org is '系统组织表'
/

comment on column sys_org.org_id is '系统组织ID'
/

comment on column sys_org.org_name is '系统组织名称'
/

comment on column sys_org.parent_id is '父组织ID'
/

comment on column sys_org.org_code is '组织编码'
/

comment on column sys_org.org_path is '组织路径'
/

comment on column sys_org.org_path_name is '组织路径名称'
/

comment on column sys_org.order_id is '排序字段'
/

模拟数据示例

select * from sys_org;

向下查找

通过父ID查询所有子节点

-- 查询通过父ID查询组织所有子节点层级信息
select t.ORG_ID,t.org_name,t.parent_id,t.org_path_name, level leaf
    from SYS_ORG t
    start with t.PARENT_ID = '0'
    connect by prior t.ORG_ID = t.PARENT_ID;

向上查找

通过子ID查询所有父节点

-- 查询子ID查询组织所有父节点层级信息
select t.ORG_ID,t.org_name,t.parent_id,t.org_path_name, level leaf
    from SYS_ORG t
    start with t.ORG_ID = '010103'
    connect by prior t.PARENT_ID = t.ORG_ID;

将同一层级组织名称放在一行

-- 查询将同一层级组织名称行转列
with tmp as (
    select t.ORG_ID,t.org_name,t.parent_id,t.org_path_name,t.ORDER_ID,level leaf
    from SYS_ORG t
    start with t.PARENT_ID = '0'
    connect by prior t.ORG_ID = t.PARENT_ID
)
select  s.leaf as 组织所属层级,
        listagg(s.org_name,',') within group ( order by s.ORDER_ID) as 同一层级组织名称
    from tmp s
    group by s.leaf;

​​listagg()​​ 行转列函数

使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)

参考博文

oracle进阶之connect by笔记

oracle递归 遍历子节点 查询父节点