关于SYS_CONNECT_BY_PATH 的一点总结

最新推荐文章于 2021-04-04 15:16:19 发布
原创 最新推荐文章于 2021-04-04 15:16:19 发布 · 925 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#path #oracle #数据库

本文介绍如何使用Oracle中的SYS_CONNECT_BY_PATH函数结合CONNECT BY子句来构建员工间的层级关系，并展示具体的SQL查询语句。
在你的数据库中执行：  
  $oracle_home/rdbms/admin/scott.sql  
  然后用scott/tiger登陆到sqlplus中，分别执行：  
   
  SELECT   SYS_CONNECT_BY_PATH(ename,   '>')   "Path"    
  FROM   scott.emp    
  START   WITH   ename   =   'KING'    
  CONNECT   BY   PRIOR   empno   =   mgr;     
    
  SELECT   ename  
  FROM   scott.emp    
  START   WITH   ename   =   'KING'    
  CONNECT   BY   PRIOR   empno   =   mgr;    
   
  看到了什么结果？  
  其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！  
  它一定要和connect   by子句合用！  
  第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！
tcl19841002
关注
在Oracle中使用SYS_CONNECT_BY_PATH的用法
Altitude_的博客
11-13 414
适用于在Oracle中的递归查询中，适用SYS_CONNECT_BY_PATH对每个节点中的数据进行拼接 例如在本案例中，查询中LPAD_GLID的数据后，将每一级节点的LPAD_GLID的数据进行拼接，放在每一级上。 例如： select xh,glid,level,LPAD (glid, 3, '0') lpad_glid,SYS_CONNECT_BY_PATH(LPAD (glid,...
postgresql中实现oracle SYS_CONNECT_BY_PATH
weixin_30332705的博客
08-03 1757
oracle： select sys_connect_by_path(t2.ID, '/') as PATH, t1.id from HTD_DEVICETYPE_RELATION t1 inner join HTD_DEVICETYPE_RESOURCE t2 on t1.CHILD_RESOURCE_TYPE_ID = t2.ID ...
Oracle之 SYS_CONNECT_BY_PATH 函数笔记
程序亦人生
11-26 4545
1、函数简介： 首先SYS_CONNECT_BY_PATH这个函数是oracle9i新提出的！ 然后这个函数要和：START WITH（非必须） 和 CONNECT BY  PRIOR（必须）联合使用；   START WITH 开始遍历的的节点   CONNECT BY PRIOR 父子关系的对应 函数有俩个参数（形成树的字段名，层级分隔符）   2、示例代码： SELECT T
SYS_CONNECT_BY_PATH 学习
天亮了
03-12 1315
上次在处理ORA-30004错误时，接触到一个新的函数SYS_CONNECT_BY_PATH 。下面对该函数进行了简单的学习、熟悉。 网上摘录（原文地址：http://www.cnblogs.com/shulmblogs/archive/2011/09/29/2195236.html）如下： Oracle函数sys_connect_by_path 详解： 语法： Oracle函
mysql拼接语句过长_执行这条SQL语句的时候报错：ORA-01489: 字符串连接的结果过长，请怎么解决 是增加字符串链接么...
weixin_42429956的博客
01-19 633
语句：WITHDM_TBLSAS(SELECTDECODE(T.DATA_TYPE,'CHAR','''''''''||'||T.COLUMN_NAME||'||''''''''','VARCHAR2','''''''''||'||T.COLUMN_NAME||'||''''''''','DATE','''TO_DATE('''''...语句：WITH DM_TBLS AS(SELECT DECO...
C连接sysbase数据库
xuezhe0的专栏
12-05 1033
#include #include #include EXEC SQL INCLUDE sqlca;int main(void){    EXEC SQL BEGIN DECLARE SECTION;    char szUserName[64];    char szPassWord[64];    char szConnection[64];    char szServerName[64];
SYS_CONNECT_BY_PATH
Java开发员
11-04 759
在Oracle中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。 sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,',')。  　　还有，
oracle sys_connect_by_path 函数 结果集连接
09-11
Oracle的`sys_connect_by_path`函数是一个非常有用的工具，尤其在构建树状结构的数据时。在本文中，我们将深入理解这个函数以及如何将其应用于非树结构的数据以生成所需的结果。 `sys_connect_by_path`函数的基本...
sys_connect_by_path的用法20220526.txt
05-27
行列转换，层级关系，oracle sys_connect_by_path的用法
SYS_CONNECT_BY_PATH 用法
一日一学！
04-15 381
SQL> SELECT DEPTNO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME, '/')), 2) PATH 2 FROM ( 3 SELECT ENAME, DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RN 4 ...
SYS_CONNECT_BY_PATH函数在9i,10G下导致执行计划改变
congdongzhu0882的博客
06-10 137
因数据库从9i到10g升级后，带SYS_CONNECT_BY_PATH 函数的sql语句导致执行计划改变原链接地址 http://www.itpub.net/viewthread.php?tid=1174233&extr...
sys_connect_by_path用法（一个select语句中多条数据拼成一条数据）
exceldba1986的专栏
08-16 278
创建表 [code="sql"] create table demoTable( id number , content varchar2(30) ); [/code] 测试数据 [code="sql"] insert into demotable (ID, CONTENT) values (1, 'content1'); insert into demo...
字符串连接超长的解决（一）
ckawt40802的专栏
04-10 523
在我的BLOG中第一篇文章写的就是字符串聚合连接的例子：http://yangtingkun.itpub.net/post/468/3380。 后来还写过一篇用SQL实现相同功能的文章：http://yangtingkun.i...
Sybase 代替start with、connect by的store procedure
人生与梦
06-11 255
if exists (select 1 from sysobjects where type='P' and name ='tree_p_lujn') DROP PROC tree_p_lujn go create proc tree_p_lujn( @is_root_start_id varchar(100), @is_table_name varchar(100), @is_co...
Oracle -- sys_connect_by_path
叶的博客
02-27 805
通过START WITH . . . CONNECT BY .. .子句来实现SQL的层次查询. 自从Oracle 9i开始，可以通过SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 自从Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。 那么你就可以
oracle 字符串 链接,字符串连接 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区
weixin_28856787的博客
04-04 150
yaanzy转的字符串求和函数我所知的原始出处是asktomlastwinner的做法是利用虚拟构造树型结构, 结合oracle提供的sys_connect_by_path来达到求和的目的上述的两个方法都不错, 但是有一个弊端, 就是会受到varchar2上限的限制, 字符串合并后的长度不能超过4000要想超过4000这个长度限制, 我们自然就想到了clob下面我将修改后的straggc函数提供给...
SYS_CONNECT_BY_PATH 函数综合应用 应用多字段拼在一行字符串
weixin_38466660的博客
12-07 1435
SELECT     f.nowdate AS "nowdate",     f.DAYOFWEEK "dayofweek",     f.WEEKOFMONTH AS "weekofmonth",     f.YEARMONTH "yearmonth",     COUNT (1) AS "messageNum",     LTRIM (         MAX (       
oracle start with connect by base_pd_clazz
如果你真的想做一件事，你一定会找到方法； 如果你不想做一件事，你一定会找到借口。
08-03 151
select lpad('test',10) from dual; 将返回“ test” select lpad('test',10,'ee') from dual; 结果将返回eeeeeetest。 [code="sql"] create or replace view base_pd_clazz as select t.cls_id, t.cls_na...
Oracle 中 将多行列值按照顺序合并成单值输出(connect by)
韩王-信
08-30 2432
使用 oracle 树形函数来处理，connect by 具体语法见：http://www.2cto.com/database/201109/106309.html 我的测试： --create a new temporary table create global temporary table weitest(id number,name varchar2(200),para num
联合查询使用SYS_CONNECT_BY_PATH
最新发布
09-02
<think> 我们正在讨论的是Oracle中的SYS_CONNECT_BY_PATH函数，它在层次查询（CONNECT BY）中使用，用于形成从根节点到当前节点的路径字符串。 用户的问题是在联合查询（UNION）中使用SYS_CONNECT_BY_PATH，这实际上是一个特定的场景，因为通常SYS_CONNECT_BY_PATH是在单个层次查询中使用。 但我们可以考虑这样的场景：将多个层次查询的结果合并在一起，每个层次查询使用SYS_CONNECT_BY_PATH生成路径。 注意：在联合查询中，每个SELECT语句都可以是一个独立的层次查询，然后在最外层使用UNION或UNION ALL合并。 例如，假设我们有两个表（或同一个表的不同部分）需要进行层次查询，并生成路径，然后将结果合并。 步骤： 1. 分别写出两个层次查询，每个查询中都可以使用SYS_CONNECT_BY_PATH。 2. 确保两个查询的列数和数据类型兼容（因为UNION的要求）。 3. 使用UNION或UNION ALL合并。 注意：在Oracle中，每个层次查询必须包含START WITH和CONNECT BY子句。 示例： 假设我们有两个表：table1和table2，它们具有相同的结构（id, parent_id, name）。我们想要分别对这两个表进行层次查询，并合并结果。 查询1：对table1进行层次查询，生成路径（以'/'分隔） 查询2：对table2进行层次查询，生成路径（同样以'/'分隔） 然后使用UNION ALL合并。 具体SQL如下： ```sql -- 查询table1的层次结构 SELECT id, name, SYS_CONNECT_BY_PATH(name, '/') AS path FROM table1 START WITH parent_id IS NULL -- 假设根节点的parent_id为空 CONNECT BY PRIOR id = parent_id UNION ALL -- 查询table2的层次结构 SELECT id, name, SYS_CONNECT_BY_PATH(name, '/') AS path FROM table2 START WITH parent_id IS NULL CONNECT BY PRIOR id = parent_id; ``` 注意：如果两个表的列不完全一致（比如列名不同），我们需要在SELECT列表中调整，使其对应。 另外，如果我们需要在联合查询的结果上进行排序等操作，可以将整个联合查询作为子查询，然后在外层进行排序。 例如，按照路径的长度排序： ```sql SELECT * FROM ( SELECT id, name, SYS_CONNECT_BY_PATH(name, '/') AS path, LENGTH(SYS_CONNECT_BY_PATH(name, '/')) AS path_length FROM table1 START WITH parent_id IS NULL CONNECT BY PRIOR id = parent_id UNION ALL SELECT id, name, SYS_CONNECT_BY_PATH(name, '/') AS path, LENGTH(SYS_CONNECT_BY_PATH(name, '/')) AS path_length FROM table2 START WITH parent_id IS NULL CONNECT BY PRIOR id = parent_id ) ORDER BY path_length; ``` 注意事项： 1. SYS_CONNECT_BY_PATH函数仅在层次查询中有效，所以每个部分都必须有CONNECT BY子句。 2. 在联合查询中，每个SELECT语句中的SYS_CONNECT_BY_PATH都会独立作用于其自身的层次结构。 3. 由于层次查询可能返回多行，联合查询的结果将是两个层次结构结果的简单合并。 另外，引用[4]中提到在MySQL中实现类似功能，但用户的问题是关于Oracle的，所以我们主要关注Oracle的使用。 如果用户需要将这样的查询转换到MySQL，可以使用递归CTE（MySQL 8.0+）和字符串连接的方式模拟，但这不是当前问题关注的。 因此，联合查询中使用SYS_CONNECT_BY_PATH的方法就是分别在每个SELECT子句（每个层次查询）中使用，然后合并。 下面我们给出一个更具体的示例，使用引用[3]中的表结构（ORGPOST_TREE表）进行联合查询： 假设我们有两个部门结构表：ORGPOST_TREE_A和ORGPOST_TREE_B，它们的结构相同（IDS, P_ID, REF_ID）。我们想要合并两个表的层次路径。 ```sql SELECT T.IDS, T.REF_ID, SYS_CONNECT_BY_PATH(T.REF_ID, '->') AS "PATH" FROM ORGPOST_TREE_A T START WITH T.P_ID = '0' CONNECT BY PRIOR T.IDS = T.P_ID UNION ALL SELECT T.IDS, T.REF_ID, SYS_CONNECT_BY_PATH(T.REF_ID, '->') AS "PATH" FROM ORGPOST_TREE_B T START WITH T.P_ID = '0' CONNECT BY PRIOR T.IDS = T.P_ID; ``` 这样我们就得到了两个表的合并层次路径结果。 注意事项： 1. 确保每个层次查询的起始条件（START WITH）和连接条件（CONNECT BY）正确。 2. 注意两个表的层次结构可能独立，所以生成的路径也是独立的。 3. 使用UNION ALL不会去重，如果两个表中有相同的节点ID，但路径不同，都会保留。如果要去重，使用UNION，但注意UNION会基于所有列去重，这可能不是我们想要的（因为路径可能不同）。 4. 性能：层次查询通常涉及递归，数据量大时可能会有性能问题，特别是在联合查询中两个表都很大时。可以考虑在父ID列上建立索引。 最后，生成相关问题。 </think> ### 联合查询中使用 SYS_CONNECT_BY_PATH 的方法及示例 #### 一、核心概念解析 **SYS_CONNECT_BY_PATH** 是 Oracle 特有的层次查询函数，用于生成从根节点到当前节点的路径字符串： 1. **语法结构**：`SYS_CONNECT_BY_PATH(column, delimiter)` 2. **依赖条件**：必须与 `START WITH...CONNECT BY PRIOR` 层次查询语句配合使用[^1][^3] 3. **典型场景**：组织机构树、分类层级、文件路径等递归结构的数据展示 #### 二、联合查询中的使用方法 在联合查询中整合层次查询结果时，需注意： ```sql -- 基础结构 (SELECT 层次查询结果 FROM 表A) UNION ALL (SELECT 层次查询结果 FROM 表B) ``` **关键要点**： 1. 每个子查询必须包含完整的 `START WITH...CONNECT BY PRIOR` 语句 2. 路径分隔符需保持一致（如 `/` 或 `->`） 3. 联合查询的列数量和类型必须兼容 #### 三、完整示例 ##### 场景：合并两个部门的组织架构树 ```sql -- 部门A的层级 SELECT employee_id, SYS_CONNECT_BY_PATH(employee_name, '->') AS org_path, LEVEL FROM dept_a START WITH manager_id IS NULL -- 从根节点开始 CONNECT BY PRIOR employee_id = manager_id UNION ALL -- 部门B的层级 SELECT employee_id, SYS_CONNECT_BY_PATH(employee_name, '->') AS org_path, LEVEL FROM dept_b START WITH manager_id = 0 -- 根节点条件不同[^4] CONNECT BY PRIOR employee_id = manager_id ORDER BY LEVEL, org_path; ``` ##### 示例输出： | EMPLOYEE_ID | ORG_PATH | LEVEL | |-------------|-------------------------|-------| | 1001 | ->CEO | 1 | | 2001 | ->CTO | 1 | | 1002 | ->CEO->Finance_Mgr | 2 | | 2002 | ->CTO->Dev_Lead | 2 | #### 四、进阶用法：路径解析 结合正则表达式拆分路径层次[^4]： ```sql SELECT org_path, REGEXP_SUBSTR(org_path, '[^/]+', 1, 1) AS level1, REGEXP_SUBSTR(org_path, '[^/]+', 1, 2) AS level2 FROM ( SELECT SYS_CONNECT_BY_PATH(dept_name, '/') AS org_path FROM department START WITH parent_dept_id IS NULL CONNECT BY PRIOR dept_id = parent_dept_id UNION ALL SELECT SYS_CONNECT_BY_PATH(dept_name, '/') FROM sub_organization START WITH parent_dept_id = 0 CONNECT BY PRIOR dept_id = parent_dept_id ); ``` #### 五、注意事项 1. **版本兼容性** - 仅 Oracle 9i+ 支持（10g 增强循环检测功能[^5]） - MySQL/PostgreSQL 需使用递归 CTE 实现类似功能 2. **性能优化** ```sql -- 为连接列创建索引 CREATE INDEX idx_manager ON dept_a(manager_id); CREATE INDEX idx_parent ON dept_b(manager_id); ``` 3. **常见错误** - `ORA-01489`: 路径字符串超过 4000 字节（需用 `SUBSTR` 截断） - `ORA-00600`: 递归层次过深（超过 Oracle 默认的 255 层限制） > 迁移提示：Oracle 到 MySQL 的转换需改用递归 CTE 和字符串拼接[^2] > ```sql > WITH RECURSIVE cte AS ( > SELECT id, name, CAST(name AS CHAR(1000)) AS path > FROM emp WHERE manager_id IS NULL > UNION ALL > SELECT e.id, e.name, CONCAT(cte.path, '->', e.name) > FROM emp e JOIN cte ON e.manager_id = cte.id > ) > SELECT * FROM cte; > ```
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值