SYS_CONNECT_BY_PATH

代码

/*
等价,相当于把每一条记录做为start with 来建立树
必须有connect by,可以没有start with
*/

select  Cow,
       Bull,
       LPAD( '   ' ,  6   *  ( Level   -   1 ))  ||  Offspring Offspring,
       Sex,
       Birthdate,
        level
   from  BREEDING
  -- start with Offspring = 'EVE'
connect  by  Cow  =  PRIOR Offspring;

select  Cow,
       Bull,
       LPAD( '   ' ,  6   *  ( Level   -   1 ))  ||  Offspring Offspring,
       Sex,
       Birthdate,
        level
   from  BREEDING
 start  with  Offspring  in  ( select  offspring  from  BREEDING)
connect  by  Cow  =  PRIOR Offspring;
/*
Cow = PRIOR Offspring;
又可以这样理解,start with 的记录的offspring 字段= 哪些记录的cow,列出那些记录
*/

代码

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  
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  

 

 

 

代码

  select  SYS_CONNECT_BY_PATH(DEPTNAME,  ' < ' ), DEPTNAME, deptno, mgrno,  level
    from  dept
 connect  by  prior deptno  =  mgrno;
  -- return 6 records

  -- MAX函数只是一个巧合按照字幕排列,我们应该是max(levle),或者长度最长
 
  select   MAX (SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,  ' < ' ),  2 ))
    from  dept
 connect  by  prior deptno  =  mgrno;

 

 

转载于:https://www.cnblogs.com/mlaaalm/archive/2010/05/17/1737486.html