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
或许应该再算下奖金，或者是年终奖，不过想想没有必要，这么拼命的做项目，数量再大也不会过超过4位数，花了几个小时学习下不足的知识点。。。以慰藉内心！
作者“java3344520的专栏”