复杂Oracle查询 限定查询+多表查询+统计查询+子查询

案例：要求查询出高于公司平均工资的雇员姓名、职位、工资、领导姓名、领导职位、部门名称、部门平均工资、工资等级，以及此等级的雇员人数。

初步分析

1. 确定要使用的数据表
   |-emp表：统计公司的平均工资；
   |-emp表：雇员姓名、职位就、工资 ；
   |-emp表：自身关联，找到领导姓名、领导职位；
   |-dept表：部门名称；
   |-emp表：统计部门人数，部门平均工资；
   |-salgrade：工资等级；
   |-emp表：统计一个工资等级的人数 ；

2.确定已知的关联字段：
|-雇员和领导：emp.mgr = memp.empno ;
|-雇员和部门：emp.deptno = dept.depnto ;

分步骤进行

第一步：计算出公司的平均工资，返回单行单列。

SELECT  AVG(sal ) FROM emp ;

第二步：找出高于此平均工资的雇员姓名，职位，工资，直接将第一步的查询作为WHERE 的限定条件。

SELECT e.ename, e.job , e.sal 
FROM emp e
WHERE e.sal>(
    SELECT   AVG(sal ) FROM emp ) ;

第三步：找到领导姓名和领导职位，直接使用emp表作为自身关联使用。

SELECT 
    e.ename 雇员姓名, e.job 雇员职位, e.sal  雇员工资 , m.ename 领导姓名 , m.job 领导职位 
FROM emp e , emp m
WHERE e.sal>(
    SELECT   AVG(sal ) FROM emp )
    AND e.mgr = m.empno(+) ;

第四步：加入dept表，找到部门名称。

SELECT 
    e.ename 雇员姓名, e.job 雇员职位, e.sal  雇员工资 ,
     m.ename 领导姓名 , m.job 领导职位  ,
    d.dname 部门名称 
FROM emp e , emp m, dept d
WHERE e.sal>(
    SELECT   AVG(sal ) FROM emp )
    AND e.mgr = m.empno(+) 
    AND e.deptno = d.deptno ;

第五步：统计部门人数，但是根据之前给出的查询，现在不可能在SELECT语句中编写COUNT函数（统计函数要么单独使用，要么结合GROUP BY 使用，唯一可能出现在SELECT里的只有统计函数和分组字段）。所以此处需要进行统计查询，但是又无法直接使用统计函数，那么就编写FROM语句之中的子查询，在FROM 里统计部门人数。

SELECT 
    e.ename 雇员姓名, e.job 雇员职位, e.sal  雇员工资 ,
     m.ename 领导姓名 , m.job 领导职位  ,
    d.dname 部门名称 ,
    dtemp.count 部门人数
FROM emp e , emp m, dept d ,(
    SELECT deptno dno, COUNT(empno) count 
    FROM emp 
    GROUP BY deptno)  dtemp
WHERE e.sal>(
    SELECT   AVG(sal ) FROM emp )
    AND e.mgr = m.empno(+) 
    AND e.deptno = d.deptno 
    AND dtemp.dno(+) = d.deptno ;

第六步：找到工资等级，直接加入salgrade表即可。

SELECT 
    e.ename 雇员姓名, e.job 雇员职位, e.sal  雇员工资 ,
     m.ename 领导姓名 , m.job 领导职位  ,
    d.dname 部门名称 ,
    dtemp.count 部门人数 ,
    s.grade 工资等级
FROM emp e , emp m, dept d ,(
    SELECT deptno dno, COUNT(empno) count 
    FROM emp 
    GROUP BY deptno)  dtemp ,salgrade s
WHERE e.sal>(
    SELECT   AVG(sal ) FROM emp )
    AND e.mgr = m.empno(+) 
    AND e.deptno = d.deptno 
    AND dtemp.dno(+) = d.deptno 
    AND e.sal BETWEEN s.losal AND s.hisal ;

第七步：找出工资等级的总体人数，依然需要编写一个子查询进行统计。

SELECT 
    e.ename 雇员姓名, e.job 雇员职位, e.sal  雇员工资 ,
     m.ename 领导姓名 , m.job 领导职位  ,
    d.dname 部门名称 ,
    dtemp.count 部门人数 ,
    s.grade 工资等级 ,
    stemp.count  等级人数
FROM emp e , emp m, dept d ,(
    SELECT deptno dno, COUNT(empno) count 
    FROM emp 
    GROUP BY deptno)  dtemp ,salgrade s ,(
    SELECT s1.grade sg , COUNT(e1.empno) count
    FROM emp e1 , salgrade s1 
    WHERE e1.sal BETWEEN s1.losal AND s1.hisal 
    GROUP BY s1.grade ) stemp 
WHERE e.sal>(
    SELECT   AVG(sal ) FROM emp )
    AND e.mgr = m.empno(+) 
    AND e.deptno = d.deptno 
    AND dtemp.dno(+) = d.deptno 
    AND e.sal BETWEEN s.losal AND s.hisal
    AND s.grade = stemp.sg ;

至此查询结束！