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

场景

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 () 将多行合并成一行(比较常用)

参考博文