CONNECT BY的几个例子

CONNECT BY START WITH

 

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

START WITH 代表你要开始遍历的的节点
CONNECT BY PRIOR 是标示父子关系的对应

 

Purpose

SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.

 

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; 

下面来自sql reference

FIRST and LAST are very similar functions. Both are aggregate and analytic
functions that operate on a set of values from a set of rows that rank as the FIRST
or LAST with respect to a given sorting specification. If only one row ranks as
FIRST or LAST, the aggregate operates on the set with only one element.
When you need a value from the first or last row of a sorted group, but the needed
value is not the sort key, the FIRST and LAST functions eliminate the need for self
joins or views and enable better performance.

The aggregate_function is any one of the MIN, MAX, SUM, AVG, COUNT,
VARIANCE, or STDDEV functions. It operates on values from the rows that rank
either FIRST or LAST. If only one row ranks as FIRST or LAST, the aggregate
operates on a singleton (nonaggregate) set.

DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle will aggregate
over only those rows with the minimum (FIRST) or the maximum (LAST)
dense rank ("olympic rank").
You can use the FIRST and LAST functions as analytic functions by specifying the
OVER clause. The query_partitioning_clause is the only part of the OVER
clause valid with these functions.

 

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;