Oracle-第9章 高级子查询

9.1 相关子查询

   相关子查询中子查询不可以单独执行的查询，一般是先执行主查询，把主查询查询到的（条件）数据送到子查询中进行数据过滤。主查询每查询到一条记录，都需要执行一次子查询进行判断。

  例1：查询比本部门平均工资高的员工编号，姓名，薪资，部门编号
  方法1：相关子查询

select empno, ename, deptno, sal
  from emp
 outer where sal > (select avg(sal) from emp where deptno = outer.deptno);

  方法2：嵌套子查询
  嵌套子查询，子查询可以单独执行
  先执行子查询，把子查询的查询结果跟主查询合并后再进行数据过滤（后执行主查询）；子查询只执行一次

  select empno, ename, e.deptno, sal, avgsal
      from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
     where e.deptno = b.deptno
       and e.sal > avgsal;

  例2：查询所有部门名称和人数
  M1：

select d.dname 部门名称, count(nvl(e.empno,0)) 部门人数
  from emp e, dept d
 where d.deptno = e.deptno
 group by d.deptno, d.dname;

  M2：

select dname, (select count(empno) from emp where deptno = d.deptno)
  from dept d;

  例3：查询哪些员工是经理?
  M1：

select * from emp where empno in (select mgr from emp);

  M2：

select *
  from emp e
 where 0 < (select count(empno) from emp where e.empno = mgr);

9.2 exists和not exists操作符

  exists和not exists
  执行相关查询时，可以使用exists或not exists是否存在查询结果，并根据判断选择主查询的数据。可以试用exists进行判断，如果子查询有数据则会把主查询当前的数据添加到显示列表（查询结果）中。

  例1：查询比本部门平均工资高的员工编号，姓名，薪资，部门编号
  M1：

select empno, ename, sal, deptno
  from emp m
 where exists
 (select '1' from emp where deptno = m.deptno having m.sal > avg(sal));

  M2：

select empno, ename, sal, deptno
  from emp e
 where sal > (select avg(sal) from emp where e.deptno = deptno);

  M3：

select empno, ename, sal, e.deptno
  from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
 where e.deptno = b.deptno
   and sal > avgsal;

  例2：查询比本部门平均工资低的员工编号，姓名，薪资，部门编号
  如果相关子查询判断数据时，如果子查询不满足或没有数据返回时，则需要输出主查询的记录，可以使用not exists进行判断。

select empno, ename, sal, deptno
  from emp m
 where not exists
 (select '1' from emp where deptno = m.deptno having m.sal > avg(sal));

9.3 练习

a).练习1
1.查询比所在职位平均工资高的员工姓名，职位。
注意是部门相同还是工作相同
方法1：

select ename, job
  from emp
 outer where sal > any (select avg(sal) from emp where job = outer.job);

方法2：

select ename, job
  from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
 where e.deptno = b.deptno
   and e.sal > b.avgsal;

2.查询工资为其部门最低工资的员工编号，姓名，工资。
方法1：相关子查询

select deptno, ename, sal
  from emp
 outer where sal = (select min(sal)
                      from emp
                     where deptno = outer.deptno
                     group by deptno);

b).方法2：

select deptno, ename, sal
  from emp
 where sal in (select min(sal) from emp group by deptno);

练习2
1.查询所有雇员编号，名字和部门名字。

select empno,ename,dname from emp e,dept d
 where e.deptno=d.deptno;

2.查询哪些员工是经理？
M1：

select ename from emp 
 where empno in(select mgr from emp);

M2：

select m.ename from emp m
 where 0<(select count(empno) from emp where m.empno=mgr);

3.查询哪些员工不是经理？
4.查询每个部门工资最低的两个员工编号，姓名，工资。

select deptno,ename,sal
  from emp e
 where(select count(*)
       from emp m
      where e.deptno=m.deptno
      and m.sal<e.sal)<2
 order by deptno,sal;

c).练习3
如下练习，用exists或not exists完成
1.列出至少有一个雇员的所有部门名称。

select dname
  from dept d
 where exists (select '1'
          from emp
         where deptno = d.deptno
         group by deptno
        having count(empno) > 0);

2.列出一个雇员都没有的所有部门名称。

select dname
  from dept d
 where not exists (select '1'
          from emp
         where deptno = d.deptno
         group by deptno
        having count(empno) > 0);

d).课后练习
1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。
M1：

select ename, deptno
  from emp m
 where sal >
       (select avg(sal) from emp where deptno = m.deptno group by deptno);

M2：

select ename,e.deptno,avgsal from emp e,
  (select deptno,avg(sal) avgsal from emp group by deptno)b
  where e.deptno=b.deptno and e.sal>avgsal;

2.查询员工姓名和直接上级的名字。

select e.ename 员工姓名, m.ename 上级姓名
  from emp e, emp m
 where e.mgr = m.empno;

3.查询每个部门工资最高的员工姓名，工资。
M1：

select ename,sal from emp 
 where sal in(select max(sal) from emp group by deptno);

M2:

select ename,sal from emp m
 where sal=(select max(sal) from emp where deptno=m.deptno);

4.查询每个部门工资前两名高的员工姓名，工资。
解题思路：只有一个工资比自己工资水平高

select deptno,ename,sal
  from emp e
 where(select count(*)
       from emp m
      where e.deptno=m.deptno
      and m.sal>e.sal)<2
 order by deptno,sal;