Oracle之复杂查询

实例1：列出薪资高于在部门30工作的所有员工的薪资的员工姓名和薪资，部门名称，部门人数。
-emp 表：姓名，薪资，部门人数；
-dept 表：部门名称；

第一步：找出30部门的所有雇员的工资，返回多行单列；

select sal from emp where deptno=30；

第二步：返回多行单列，只能在where子句里面使用子查询（in，any, all），应该使用>all ，找到员工的姓名，薪资；

select e.ename, e.sal
from emp e
where sal>all(
  select sal 
  from emp
  where deptno=30 );

第三步：还需查找到部门信息，，在from子句之后引入dept表（一定存在关联字段或关联条件）；

select e.ename, e.sal ，d.dname
from emp e, dept d
where sal>all(
    select sal 
    from emp
    where deptno=30 ) 
    AND e.deptno=d.deptno ;

第四步：随后要统计部门人数

select deptno dno , count(empno) count
from emp
group by deptno;

第五步：以上查询返回的是多行多列的数据，按照使用原则应该在from子句中使用。

select e.ename, e.sal ，d.dname, temp.count
from emp e, dept d, (
    select deptno dno , count(empno) count
    from emp
    group by deptno ) temp
where sal>all(
    select sal 
    from emp
    where deptno=30 )
    AND e.deptno=d.deptno 
    AND temp.dno=d.deptno ;

实例2：列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数，领导姓名。

第一步：找到scott的工作，返回单行单列，一般用于where或having 上，此处没有统计需要，所以只在where子句上使用；

 select job from  emp where ename='SCOTT';

第二步：找到符合此要求的雇员信息；

select e.ename, e.job ,e.sal
from emp e
where e.job=(
    select job from  emp where ename='SCOTT')；

第三步：查找到对应的部门名称信息，并消除笛卡尔积；

select e.ename, e.job ,e.sal, d.dname
from emp e, dept d
where e.job=(
    select job from  emp where ename='SCOTT')
    AND e.deptno=d.deptno；

第四步：部门人数单独进行统计操作；

select e.ename, e.job ,e.sal, d.dname ,temp.count
from emp e, dept d,(
     select deptno dno,count(empno) count
     from emp
     group by deptno) temp
where e.job=(
    select job from  emp where ename='SCOTT')
    AND e.deptno=d.deptno
    AND d.deptno=temp.dno；

第五步：查找出雇员对应的领导信息，使用emp，自身关联操作；

select e.ename, e.job ,e.sal, d.dname ,temp.count, m.ename
from emp e, dept d,(
    select deptno dno,count(empno) count
    from emp
    group by deptno) temp ,emp m
where e.job=(
    select job from  emp where ename='SCOTT')
    AND e.deptno=d.deptno
    AND d.deptno=temp.dno
    AND e.mgr=m.empno；

第六步：消除掉scott数据

 select e.ename, e.job ,e.sal, d.dname ,temp.count, m.ename
 from emp e, dept d,(
     select deptno dno,count(empno) count
     from emp
     group by deptno) temp ,emp m
 where e.job=(
     select job from  emp where ename='SCOTT')
     AND e.deptno=d.deptno
     AND d.deptno=temp.dno
     AND e.mgr=m.empno
     AND e.ename<>'SCOTT'；

实例3：列出所有“CLERK”(办事员)的姓名及其部门名称。部门人数，工资等级。

第一步：找到所有办事员的姓名；

select e.ename from emp e where e.job='CLERK';

第二步：找到部门名称；

select e.ename,d.dname
from emp e ,dept d
where e.job='CLERK'
    AND e.edptno=d.deptno;

第三步：统计部门人数；

select e.ename,d.dname,temp.count
from emp e ,dept d，(
    select deptnno dno,count(empno) count
    from emp
    group by deptno) temp
where e.job='CLERK'
    AND e.edptno=d.deptno
    AND d.deptno=temp.dno;

第四步：查询工资等级；

select e.ename,d.dname,temp.count，s.grade
from emp e ,dept d，(
    select deptnno dno,count(empno) count
    from emp
    group by deptno) temp,salgrade s
where e.job='CLERK'
    AND e.edptno=d.deptno
    AND d.deptno=temp.dno
    AND e.sal BETWEEN s.losal AND s.hisal;