SYS_CONNECT_BY_PATH函数用法 .

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

   --得到结果为：

   KING
   JONES
   SCOTT
   ADAMS
   FORD
   SMITH
   BLAKE
   ALLEN
   WARD
   MARTIN
   TURNER
   JAMES

    

   而：

    

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

    

   --得到结果为：

    

   >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
    

   
   其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！
   它一定要和connect by子句合用！
   第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

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

   CONNECT BY PRIOR 是标示父子关系的对应！

   如下例子：

   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 ='AA_TEST')  
   start with rn=1 connect by rn=rownum ; 
   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、带层次关系

   view plaincopy to clipboardprint?
   SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);  
    
   Table created.  
    
   SQL> insert into dept values(1,'总公司',null);  
    
   1 row created.  
    
   SQL> insert into dept values(2,'浙江分公司',1);  
    
   1 row created.  
    
   SQL> insert into dept values(3,'杭州分公司',2);  
    
   1 row created.  
    
   SQL> commit;  
    
   Commit complete.  
    
   SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept connect by prior deptno=mgrno;  
    
   MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))  
   --------------------------------------------------------------------------------  
   总公司,浙江分公司,杭州分公司 
   SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);

   Table created.

   SQL> insert into dept values(1,'总公司',null);

   1 row created.

   SQL> insert into dept values(2,'浙江分公司',1);

   1 row created.

   SQL> insert into dept values(3,'杭州分公司',2);

   1 row created.

   SQL> commit;

   Commit complete.

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

   MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))
   --------------------------------------------------------------------------------
   总公司,浙江分公司,杭州分公司

   2、行列转换

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

   view plaincopy to clipboardprint?
   SQL> 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