ORACLE-SYS_CONNECT_BY_PATH函数汇总

SYS_CONNECT_BY_PATH(column_name,'分隔符')函数

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符

伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

示例1：查询表中树结构并转化为文本展示（转载）

SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ou_code, ';')), ';') AS RESULT
      FROM (SELECT category_id, ou_code, RN, LEAD(RN) OVER(ORDER BY RN) RN1
              FROM (SELECT category_id,
                           ou_code,
                           ROW_NUMBER() OVER(ORDER BY category_id, ou_code DESC) RN
                      FROM (SELECT t.category_id, t.ou_code
                              FROM bpm_dcs_category_ou t
                             WHERE t.category_id=1000085)))
     START WITH RN1 IS NULL
            AND category_id = 1000085
    CONNECT BY RN1 = PRIOR RN;

示例2：列转行（转载）

view plaincopy to clipboardprint
select max(substr(sys_connect_by_path(column_name,','),2))  
from (select column_name,rownum rn from user_tab_columns where table_name ='DEPT')  
start with rn=1 connect by rn=rownum ;  
 
MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))  
--------------------------------------------------------------------------------  
DEPTNO,DEPTNAME,MGRNO  

MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,';'),2)) 先连接所有节点，从第二个字符开始截取，取最完整条目

其作用于LTRIM(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME,';')),';')（先连接所有节点，取最完整条目，删除左侧字符‘;’）类似

补充：结构化查询

START WITH ...CONNNECT BY PRIOR基本语法是：

SELECT ...FROM

START WITH (根节点，可以指定多个节点)
CONNECT BY PRIOR= （连接条件，PRIOR置于等号前，则从根节点到叶节点开始检索；置于等号后，则从叶节点到根节点开始检索）
WHERE （过滤返回记录，仅过滤被限定节点，其根节点和子节点均不受影响）

该查询访问路径如下：

从根节点开始，向下扫描子节点，该子节点已被访问则转向其最左侧未被访问的子节点，否则判断该节点是否为根节点，是则访问完毕，否则返回父节点重新执行判断。

即扫描整表判断所有节点树结构（遍历树结构）。