SYS_CONNECT_BY_PATH 函数用法

SYS_CONNECT_BY_PATH 函数用法


 SELECT ename
   FROM emp
  START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;

ENAME
----------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

14 rows selected.



 SELECT SYS_CONNECT_BY_PATH(ename, '>') "Path"
   FROM emp
  START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;

Path
---------------------------------------------
>KING
>KING>JONES
>KING>JONES>SCOTT
>KING>JONES>SCOTT>ADAMS
>KING>JONES>FORD
>KING>JONES>FORD>SMITH
>KING>BLAKE
>KING>BLAKE>ALLEN
>KING>BLAKE>WARD
>KING>BLAKE>MARTIN
>KING>BLAKE>TURNER
>KING>BLAKE>JAMES
>KING>CLARK
>KING>CLARK>MILLER

14 rows selected.


SYS_CONNECT_BY_PATH 函数是oracle 9i才新提出来的. 它一定要和 connect by 子句合用.
第一个参数是形成树形式的字段, 第二个参数是父级和其子级分隔显示用的分隔符.

START WITH 代表要开始遍历的的节点.

CONNECT BY PRIOR 表示父子关系的对应

例子:

 select max(substr(sys_connect_by_path(column_name,','),2))
   from (
         select column_name,rownum rn
           from user_tab_columns
          where table_name ='AA_TEST'
        )
  start with rn=1
connect by rn=rownum ;


将列进行分割成为一行, 然后将首个去掉, 只取最大的那个数据.

---------------------------------------------

例子:

1. 带层次关系


SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);

Table created.

SQL> insert into dept values(1,'Head office',null);

1 row created.

SQL> insert into dept values(2,'zhejiang branch',1);

1 row created.

SQL> insert into dept values(3,'hangzhou branch',2);

1 row created.

SQL> commit;

Commit complete.

 select max(substr(sys_connect_by_path(deptname,','),2))
   from dept
connect by prior deptno=mgrno
/

MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))
-----------------------------------------------------------
zhejiang branch,hangzhou branch


 select max(substr(sys_connect_by_path(deptname, ','),2))
   from dept
  start with deptname = 'Head office'
connect by prior deptno = mgrno
/

MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))
-----------------------------------------------------------
Head office,zhejiang branch,hangzhou branch




2. 行列转换

如把一个表的所有列连成一行, 用逗号分隔:

 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

 select deptno,
        ltrim(sys_connect_by_path(ename, ','),',') emps 
   from (
          select deptno,
                 ename, 
                 row_number() over(partition by deptno order by empno) rn,
                 count(*) over(partition by deptno) cnt
            from emp
        )
  where level = cnt
  start with rn = 1
connect by prior deptno = deptno
    and prior rn = rn - 1


    DEPTNO EMPS
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES