Oracle -- CONNECT BY的几个例子

LEVEL是伪列，用来表示该条记录位于树形结构的第几层

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

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

SELECT last_name, employee_id, manager_id, LEVEL
 FROM employees
 START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
 ORDER SIBLINGS BY last_name;

  SELECT LPAD(' ', 5 * LEVEL, ' ') || ename empName,
         dname,
         job,
         sys_connect_by_path(ename, '/') cbp
    FROM scott.emp e, scott.dept d
   WHERE e.deptno = d.deptno
   START WITH mgr IS NULL
  CONNECT BY PRIOR empno = mgr
   ORDER SIBLINGS BY job;
EMPNAME          DNAME          JOB       CBP
---------------- -------------- --------- ---------------------------
  KING           ACCOUNTING     PRESIDENT /KING
    JONES        RESEARCH       MANAGER   /KING/JONES
      SCOTT      RESEARCH       ANALYST   /KING/JONES/SCOTT
        ADAMS    RESEARCH       CLERK     /KING/JONES/SCOTT/ADAMS
      FORD       RESEARCH       ANALYST   /KING/JONES/FORD
        SMITH    RESEARCH       CLERK     /KING/JONES/FORD/SMITH
    CLARK        ACCOUNTING     MANAGER   /KING/CLARK
      MILLER     ACCOUNTING     CLERK     /KING/CLARK/MILLER
    BLAKE        SALES          MANAGER   /KING/BLAKE
      JAMES      SALES          CLERK     /KING/BLAKE/JAMES
      ALLEN      SALES          SALESMAN  /KING/BLAKE/ALLEN
      WARD       SALES          SALESMAN  /KING/BLAKE/WARD
      TURNER     SALES          SALESMAN  /KING/BLAKE/TURNER
      MARTIN     SALES          SALESMAN  /KING/BLAKE/MARTIN


自从Since Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示：
如HN项目，取SWJG_LJ

select vjg.swjg_dm,
       vjg.sj_swjg_dm,
       vjg.swjg_lj swjg_lj1,
       '14100000000' || sys_connect_by_path(swjg_dm, '#') swjg_lj2,
       sys_connect_by_path(swjg_dm, '#') swjg_lj3
  from ssfxpgcl.dm_swjg vjg
 start with vjg.swjg_dm = '14101000000'
connect by prior swjg_dm = sj_swjg_dm

复杂的树型结构――多列变单列

树型结构也分单树和多树(我的称呼,实际上就是指单支和多支)
对于下面的这种情况， 我们必须要构造的树就属于单支树。
原始环境如：

select * from test;
结果为：
1        n1
1        n2
1        n3
1        n4
1        n5
3        t1
3        t2
3        t3
3        t4
3        t5
3        t6
2        m1



造树
脚本如下：
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test
结果如下：
No  Q  RN RN1
1        n1        2        1
1        n2        3        2
1        n3        4        3
1        n4        5        4
1        n5        6        5
2        m1        8        1
3        t1        10        1
3        t2        11        2
3        t3        12        3
3        t4        13        4
3        t5        14        5
3        t6        15        6


每列的目的是：

RN1列主要的目的是分组， 按照value值‘1’，我们可以start with使用它。

RN列主要用来做connect by使用。 实际上它就是我们要的树。

第一个支： 2，3，4，5，6

第二个支： 8

第三个支： 10，11，12，13，14，15

中间为什么要断掉：7,9  目的就是为了区别每个分支。 到后面看具体的SQL，就明白这里的说法了。

杀手锏

既然我们有了树， 就可以使用树型函数SYS_CONNECT_BY_PATH和connect by啦，来拼接我们所需要的多列值。

脚本如下：
select no,sys_connect_by_path(q,',')
from (
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn
结果为：
1        ,n1
1        ,n1,n2
1        ,n1,n2,n3
1        ,n1,n2,n3,n4
1        ,n1,n2,n3,n4,n5
2        ,m1
3        ,t1
3        ,t1,t2
3        ,t1,t2,t3
3        ,t1,t2,t3,t4
3        ,t1,t2,t3,t4,t5
3        ,t1,t2,t3,t4,t5,t6
终极武器

终极武器
最终我们要的值，是单列值， 其实想想， 也就是最长的一行咯。 那么就好办了。 我们直接GROUP BY ，然后取MAX值。
脚本如下：
select no,max(sys_connect_by_path(q,','))
from (
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn 
group by no
结果为：
1        ,n1,n2,n3,n4,n5
2        ,m1
3        ,t1,t2,t3,t4,t5,t6

如果觉得前面的‘，’不好看，可以使用ltrim去掉。 或者用substr也可以。

如下：

ltrim(max(sys_connect_by_path(q,',')),',')

或者

substr(max(sys_connect_by_path(q,',')),2)

例如
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;
DEPTNO ENAME
------ ----------
    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD
14 rows selected.
想输出为：
DEPTNO ENAME
------ ----------
    10 CLARK, KING, MILLER
    20 ADAMS, FORD, JONES, SCOTT, SMITH
    30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 
除了使用聚集函数或者存储过程之外，
9i中可以：
SQL>    SELECT deptno,
           LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ','))
                 KEEP(DENSE_RANK LAST ORDER BY curr),
                 ',') AS concatenated
      FROM (SELECT deptno,ename,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS curr,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) - 1 AS prev
              FROM scott.emp)
     GROUP BY deptno
    CONNECT BY prev = PRIOR curr
           AND deptno = PRIOR deptno
     START WITH curr = 1; 
以下是几个例子：

SELECT department_id,
  MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY employee_id desc) "Worst1",
  MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY employee_id ) "Worst2",
  MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY first_name ) "Worst3",
  MIN(salary) KEEP(DENSE_RANK last ORDER BY first_name ) "Worst4",
  MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
  MAX(salary) KEEP(DENSE_RANK LAST ORDER BY commission_pct) "Best"
  FROM employees
 GROUP BY department_id;


SELECT last_name,
       department_id,
       salary,
       MIN(salary) KEEP(DENSE_RANK FIRST ORDER BY commission_pct) OVER(PARTITION BY department_id) "Worst",
       MAX(salary) KEEP(DENSE_RANK LAST ORDER BY commission_pct) OVER(PARTITION BY department_id) "Best"
  FROM employees
 ORDER BY department_id, salary;
SELECT deptno,
           LTRIM(MAX(SYS_CONNECT_BY_PATH(ename, ','))
                 KEEP(DENSE_RANK LAST ORDER BY curr),
                 ',') AS concatenated
      FROM (SELECT deptno,ename,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS curr,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) - 1 AS prev
              FROM scott.emp)
     GROUP BY deptno
    CONNECT BY prev = PRIOR curr
           AND deptno = PRIOR deptno
     START WITH curr = 1;
出处：http://blog.chinaunix.net/uid-401933-id-2407690.html