ORACLE 树查询

昨天CSDN里处理问题的时候，发现了一个语法connect_by_root，眼前一亮，好像没有见过，经常用的只是connect by ，于是翻开文档重新学习了下

先看自带的SCHEMA的SCOTT的数据：
SQL>SELECT e.empno,e.ename,e.mgr,e.deptno  FROM emp e;

EMPNO ENAME     MGR DEPTNO
7369 SMITH 7902 20
7499 ALLEN 7698 30
7521 WARD 7698 30
7566 JONES 7839 20
7654 MARTIN 7698 30
7698 BLAKE 7839 30
7782 CLARK 7839 10
7788 SCOTT 7566 20
7839 KING  10
7844 TURNER 7698 30
7876 ADAMS 7788 20
7900 JAMES 7698 30
7902 FORD 7566 20
7934 MILLER 7782 10
 

阶层查询温习

■PRIOR
阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点，
作为运算符，PRIOR和加(+)减(-)运算的优先级相同。 这个很重要，重点理解下：

prior empno= mgr

意思是：祖先（上一层记录）的empno等于本条记录的mgr，即：通过根节点遍历子节点

 

■阶层查询
语法：START WITH condition CONNECT BY NOCYCLE condition

START WITH 指定阶层的根
CONNECT BY 指定阶层的父/子关系
NOCYCLE 存在CONNECT BY LOOP的纪录时，也返回查询结果。
condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr

 

■CONNECT BY子句的例子
通过CONNECT BY子句定义职员和上司的关系。

 

■LEVEL的例子
通过LEVEL虚拟列表示节点的关系。

 

从上级往下级够造树查询（默认排序）：

这个公司的老大是KING，

7839 KING  10
因为他没有上级，按照EMPNO和MGR排下层级关系：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL
  FROM EMP E
 START WITH E.EMPNO = 7839
CONNECT BY PRIOR E.EMPNO = E.MGR;

EMPNO ENAME   MGR   DEPTNO   LEVEL
7839 KING  10 1
7566 JONES 7839 20 2
7788 SCOTT 7566 20 3
7876 ADAMS 7788 20 4
7902 FORD 7566 20 3
7369 SMITH 7902 20 4
7698 BLAKE 7839 30 2
7499 ALLEN 7698 30 3
7521 WARD 7698 30 3
7654 MARTIN 7698 30 3
7844 TURNER 7698 30 3
7900 JAMES 7698 30 3
7782 CLARK 7839 10 2
7934 MILLER 7782 10 3
可以很清晰的看到层级关系，同时也可以看到结果按照DEPTNO和LEVEL的排序，看到部门的组成层级关系，如果我这样得到组织机构了，我还想按人名排序呢，因为默认的排序是按照EMPNO来进行的，此时：

 

■START WITH子句的排序
通过START WITH指定根节点，ORDER SIBLINGS BY保持阶层的顺序。

 

从上级往下级够造树查询（按组织的人名排序）：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL
  FROM EMP E
 START WITH E.EMPNO = 7839
CONNECT BY PRIOR E.EMPNO = E.MGR
 ORDER SIBLINGS BY E.ENAME;

EMPNO ENAME   MGR   DEPTNO   LEVEL
7839 KING  10 1
7698 BLAKE 7839 30 2
7499 ALLEN 7698 30 3
7900 JAMES 7698 30 3
7654 MARTIN 7698 30 3
7844 TURNER 7698 30 3
7521 WARD 7698 30 3
7782 CLARK 7839 10 2
7934 MILLER 7782 10 3
7566 JONES 7839 20 2
7902 FORD 7566 20 3
7369 SMITH 7902 20 4
7788 SCOTT 7566 20 3
7876 ADAMS 7788 20 4
 

从下往上查询组织关系，例如我想知道

7369 SMITH 7902 20 4 他上面有几个领导，哪个是直属领导呢（会干活的人，领导一般就是多，我是其中之一，杯具，不过发展空间多，虽然累,自我激励下），这个时候如何查呢：
SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL
  FROM EMP E
 START WITH E.EMPNO = 7369
CONNECT BY PRIOR E.MGR = E.EMPNO
 ORDER SIBLINGS BY E.ENAME;

EMPNO ENAME MGR DEPTNO LEVEL
7369 SMITH 7902 20 1
7902 FORD 7566 20 2
7566 JONES 7839 20 3
7839 KING  10 4
领导的个数和我差不多。。哈哈，还有一种情况，我们公司这种家族企业，虽然再人事组织关系上，领导关系鲜明，但是，例如SIMITH是最大领导KING的老婆，那么实际上的关系可能是这样的，SIMIT虽然听从FORD的工作安排命令，但是确可以直接安排老总KING，那么我将家族这种特殊的关系引入到组织机构里会怎样呢，请看：

先做下更新，将KING的领导MGR更新为SMITH的EMPNO：

UPDATE emp SET mgr = 7369 WHERE empno = 7839;

这个时候再去查询SMITH有多少个老大的时候就会无限制循环了，看看结果如何：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL
  FROM EMP E
 START WITH E.EMPNO = 7369
CONNECT BY PRIOR E.MGR = E.EMPNO
 ORDER SIBLINGS BY E.ENAME
 
ORA-01436: CONNECT BY loop in user data

 

报错了原因是因为产生了CONNECT BY LOOP，这个时候怎么办呢，取组织关系的时候加上 NOCYCLE，试试就知道了：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL
  FROM EMP E
 START WITH E.EMPNO = 7369
CONNECT BY NOCYCLE PRIOR E.MGR = E.EMPNO
 ORDER SIBLINGS BY E.ENAME;

得到了正确的结果，开来家族企业也得公私分开啊，不然只能停滞不前啊。

 

例如我们想看二把手JONES在公司的地位，关键看他领导了几个部门，最重要的是什么部门。例如财务部是生杀大权的诞生地，采购部是油水回流地，查查看：

SQL>

SELECT ENAME "Employee",
       CONNECT_BY_ISCYCLE "Cycle",
       EMPNO,
       MGR,
       LEVEL,
       SYS_CONNECT_BY_PATH(ENAME, '/') "Path"
  FROM EMP
 WHERE LEVEL <= 5
   AND DEPTNO = 20
 START WITH ENAME = 'JONES'
CONNECT BY NOCYCLE PRIOR EMPNO = MGR
       AND LEVEL <= 5;

Employee Cycle EMPNO MGR LEVEL Path
JONES 0 7566 7839 1 /JONES
SCOTT 0 7788 7566 2 /JONES/SCOTT
ADAMS 0 7876 7788 3 /JONES/SCOTT/ADAMS
FORD 0 7902 7566 2 /JONES/FORD
SMITH 0 7369 7902 3 /JONES/FORD/SMITH
底下一共有4名员工，员工的领导关系一目了然。

 

如果我想知道这个公司每个部门每个月要发多少工资，首先明确10部门是KING所在的部门，这个部门相当于管理部门，负责管理底下所有的部门，这个时候怎么办？好的，看看下面的：

SQL>SELECT NAME, SUM(SAL) AS SAL
  FROM (SELECT CONNECT_BY_ROOT ENAME AS NAME, ENAME, LEVEL, SAL
          FROM EMP
         WHERE DEPTNO = 10
        CONNECT BY NOCYCLE PRIOR EMPNO = MGR)
 GROUP BY NAME;

NAME            SAL
MILLER         1300
CLARK          3750
KING              8750

 

OK,温习完毕，再来解答帖子里的这个问题

http://topic.csdn.net/u/20110928/11/1306858a-ba5d-4d71-b7c2-984bc3fd20f7.html

大致转换到SCOTT表里的需求为：查2把手的工资总和 或者说给定条件，查询下一层级的工资总和

这里是老总KING要给他的直接下属总共发多少工资？如下SQL：

SQL>SELECT EMPNO, SUM(SAL) AS TOTAL_SAL
  FROM (SELECT CONNECT_BY_ROOT E.EMPNO AS EMPNO,
               E.ENAME,
               E.SAL,
               LEVEL AS ILEVEL
          FROM EMP E
         WHERE LEVEL = 2
         START WITH E.EMPNO = 7839
        CONNECT BY PRIOR E.EMPNO = E.MGR)
 GROUP BY EMPNO;

 

EMPNO                TOTAL_SAL
7839                       8275