connect by

例子：

fuyou@ORCL> select deptno,ename,row_number() over(partition by deptno order by empno)rn
  2  ,count(*)over(partition by deptno) cnt
  3  from emp;

    DEPTNO ENAME                        RN        CNT
---------- -------------------- ---------- ----------
        10 CLARK                         1          2
        10 KING                          2          2
        20 SMITH                         1          5
        20 JONES                         2          5
        20 SCOTT                         3          5
        20 ADAMS                         4          5
        20 FORD                          5          5
        30 ALLEN                         1          6
        30 WARD                          2          6
        30 MARTIN                        3          6
        30 BLAKE                         4          6
        30 TURNER                        5          6
        30 JAMES                         6          6
        40 MILLER                        1          1

fuyou@ORCL> select deptno,sys_connect_by_path(ename,',') as emps
  2  from (select deptno,ename,row_number() over(partition by deptno order by empno)rn
  3  ,count(*)over(partition by deptno) cnt
  4  from emp)
  5  where level=cnt
  6  connect by prior deptno = deptno and prior rn = rn-1
  7  ;

    DEPTNO
----------
EMPS
      10
,CLARK,KING

        20
,SMITH,JONES,SCOTT,ADAMS,FORD

        30
,ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

        40
,MILLER

fuyou@ORCL> select deptno,ltrim(sys_connect_by_path(ename,',') ,',')as emps
  2  from (select deptno,ename,row_number() over(partition by deptno order by empno)rn
  3  ,count(*)over(partition by deptno) cnt
  4  from emp)
  5  where level=cnt
  6  connect by prior deptno = deptno and prior rn = rn-1
  7  ;

    DEPTNO
----------
EMPS

        10
CLARK,KING

        20
SMITH,JONES,SCOTT,ADAMS,FORD

        30
ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

        40
MILLER

在此例子中，使用rn来作为遍历树
start with rn=1 --rn=1的为根 并为每遇到rn=1，创建一个新的列表

对使用了connect by 的level从1开始

connect by prior deptno = deptno and prior rn = rn-1
//上一行记录的deptno 等于 下一行的deptno 的deptno 且上一行的rn 等于下一行的rn-1

sys_connect_by_path使用迭代方式构建此列表
sys_connect_by_path 会在列表最前面增加一个所选定的分隔符，每迭代一次会在当前的列加增加分隔符

另一例子：

fuyou@ORCL> select rpad('*',2*level,'*')||ename ename
  2  from emp
  3  start with mgr is null--mgr 为空为根
  4  connect by prior empno = mgr;--前一行的empno 等于下一行的mgr 以这样的方式来构建

ENAME

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

已选择14行。

fuyou@ORCL> spool off

下面这个例子容易明白

fuyou@ORCL>  select rpad('*',2*level,'*')||ename ename,empno,mgr
  2    from emp
  3    start with mgr is null
  4    connect by prior empno = mgr;

ENAME

     EMPNO        MGR
---------- ----------
**KING
      7839

****JONES
      7566       7839

******SCOTT
      7788       7566

********ADAMS
      7876       7788

******FORD
      7902       7566

********SMITH
      7369       7902

****BLAKE
      7698       7839

******ALLEN
      7499       7698

******WARD
      7521       7698

******MARTIN
      7654       7698

******TURNER
      7844       7698

******JAMES
      7900       7698

****CLARK
      7782       7839

******MILLER
      7934       7782


已选择14行。

fuyou@ORCL> spool off