CONNECT_BY_ROOT

1.select * from  EMP t  where t.deptno = 10;

 

EMPNO     ENAME     JOB     MGR     HIREDATE     SAL     COMM     DEPTNO
7782     CLARK     MANAGER     7839     1981/6/9     2450.00          10
7839     KING     PRESIDENT            1981/11/17       5000.00          10
7934     MILLER     CLERK     7782     1982/1/23      1300.00          10

 

2.使用connect_by_root,显示名字和薪水，如果有下属，那么还显示上司的名字和下属薪水的组合

 

SELECT CONNECT_BY_ROOT t.ename as name , t.sal

FROM emp t

WHERE t.deptno = 10

CONNECT BY PRIOR t.empno = t.mgr;

 

 NAME         SAL
MILLER     1300.00
CLARK      2450.00
CLARK      1300.00
KING        5000.00
KING        2450.00
KING        1300.00

 

3.下面我就可以利用上面的铺垫查询每个人管理的组(他们团队的平均薪水了)

 

SELECT name ,avg(sal)

FROM

    (SELECT CONNECT_BY_ROOT t.ename as name, t.sal

     FROM emp t

     WHERE t.deptno = 10

     CONNECT BY PRIOR t.empno = t.mgr

    )

GROUP BY name ;

 

NAME     AVG(SAL)
MILLER     1300                                    --他是最底层的码农，没有下属
CLARK      1875                                    --他管了一个人，那个码农，平均薪水(2450 + 1300) / 2
KING        2916.66666666667                --他是部门10的boss，两个下属

 

 

转自： http://blog.chinaunix.net/uid-26896647-id-3424394.html