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,两个下属
阅读(654) | 评论(0) | 转发(0) |