Oracle 高级函数 SYS_CONNECT_BY_PATH

最新推荐文章于 2021-04-10 17:30:19 发布

原创最新推荐文章于 2021-04-10 17:30:19 发布 · 682 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

12 篇文章

订阅专栏

本文介绍Oracle中SYS_CONNECT_BY_PATH函数的使用方法，用于实现层次结构数据查询及多列转换为行的处理。通过具体示例，展示如何构建层次结构并获取特定层级的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

语法：
Oracle函数:SYS_CONNECT_BY_PATH主要用于层次查询)以及多列转行。
用法：
select … sys_connect_by_path(column_name,’/’) from table
start with … connect by … prior

1.测试数据

With tt as (
 select 1 id,'AA' name, 0 pid, 1 level_ from dual
 UNION  select 2 id,'BB' name, 1 pid ,2 level_ from dual
 UNION  select 3 id,'CC' name, 1 pid ,2 level_ from dual
 UNION  select 4 id,'DD' name, 1 pid ,2 level_ from dual
 UNION  select 5 id,'EE' name, 2 pid ,3 level_ from dual
 UNION  select 6 id,'FF' name, 3 pid ,3 level_ from dual
 UNION  select 7 id,'GG' name, 4 pid ,3 level_ from dual
 UNION  select 8 id,'HH' name, 4 pid ,3 level_ from dual
)

2.执行函数

select SYS_CONNECT_BY_PATH(tt.name, '/') ,id,name ,level_ from tt
start with tt.id = 1
connect by prior tt.id = tt.pid;

3.测试结果：
在这里插入图片描述

Level 关键字获取第2层级数据

 With tt as (
 select 1 id,'AA' name, 0 pid, 1 level_ from dual
 UNION  select 2 id,'BB' name, 1 pid ,2 level_ from dual
 UNION  select 3 id,'CC' name, 1 pid ,2 level_ from dual
 UNION  select 4 id,'DD' name, 1 pid ,2 level_ from dual
 UNION  select 5 id,'EE' name, 2 pid ,3 level_ from dual
 UNION  select 6 id,'FF' name, 3 pid ,3 level_ from dual
 UNION  select 7 id,'GG' name, 4 pid ,3 level_ from dual
 UNION  select 8 id,'HH' name, 4 pid ,3 level_ from dual
)
select SYS_CONNECT_BY_PATH(tt.name, '/') ,id,name ,level_ from tt where level=2
start with tt.id = 1  
connect by prior tt.id = tt.pid;

在这里插入图片描述