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