Oracle相关子查询和分级查询举例

1.相关子查询

--类似not in的查询条件会受到子查询中null值的影响，通常会排除子查询中的空值
SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL);
 
--有一种查询，出现了本部门，本公司之类的字眼，通常需要用到相关子查询
--输出员工的姓名，工资，和本部门的平均工资
--使用别名，将外层查询的表与内层查询的表区分开
select ename,sal,(select avg(sal) from emp e2 where e2.deptno=e1.deptno)
from emp e1;

--练习，输出 员工姓名，他的经理所在部门的平均工资
--查询的嵌套可以有多层
SELECT ename,
       (SELECT AVG(sal)
          FROM emp
         WHERE deptno = (SELECT deptno FROM emp e2 WHERE e2.empno = e1.mgr))
  FROM emp e1;

2. 分级查询

--exit 的用法
--特点：只关心子查询有没有匹配值，并不关心返回的值是什么
SELECT 'ok' FROM dual 
WHERE not EXISTS (SELECT 'cvvbghfgjhjtyjjtyj' FROM dual WHERE 1 = 1);

--相关子查询找出员工编号出现在经理这一栏的人，返回任意值
--外层查询只要有返回值，就判定这个人是经理，将他输出
SELECT ename,empno
FROM   emp outer
WHERE  EXISTS ( SELECT 'X'
                 FROM   emp
                 WHERE  mgr = 
                        outer.empno);
                        
--with子句
--将子查询的结果保存下来，在后面可以直接使用
--适合某一个子查询结果在多个地方反复用到的情况

--查询工资高于本部门平均工资的人
with 
dept_avgsal as (select deptno,avg(sal) avgs from emp group by deptno)

select ename,sal,avgs
from emp e,dept_avgsal d
where e.deptno=d.deptno
and e.sal>d.avgs

--分级查询
--start with指定开始节点
--connect by prior指定层次关系
--层次关系等号两边的顺讯决定向上搜寻还是向下搜寻

--符合过滤条件的员工和他的下属会被隐藏
select level,ename,mgr
from emp
start with ename='KING'
connect by prior empno=mgr and ename!='JONES'
ORDER BY level;

--符合过滤条件的员工被隐藏，他的下属会被显示
select level,ename,mgr
from emp
where ename!='JONES'
start with ename='KING'
connect by prior empno=mgr 
ORDER BY level;