复杂查询案例

1.案例一

列出薪金高于在部门30工作的所有员工姓名和薪金、部门名称、部门人数。

• 确定要使用的数据表：

  |-emp表：员工姓名和薪金

  |-dept表：部门名称

  |-emp表：统计出部门人数

• 确定已知的关联字段：

  |-员工与部门：emp.deptno=dept.deptno

第一步：找到30部门所有雇员的薪金

第二步：以上查询中返回的是多行单列的数据，那么此时就可以使用三种判断符判断：IN ,ANY,ALL

现找到的是所有员工，使用“>ALL”.

第三步：要找到部门的信息，自然在FROM子句之后引入dept表，而后消除笛卡尔积（内连接）

第四步：需要统计出部门人数的信息

思考：

• 如果要进行部门的人数统计，那么一定要按照部门分组

• 在使用分组的时候，SELECT子句只能够出现分组字段与统计函数

  于是此时就出现了矛盾，因为SELECT子句里面有其他的字段，所以不可能直接使用GROUP BY分组，可以考虑利用子查询分组，即：在FROM子句之后使用子查询先进行分组统计，而后将临时表继续采用多表查询操作

  

2.案例二

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

• 确定要使用的数据表：

  |-emp表：员工信息

  |-dept表：部门名称

  |-emp表：领导信息

• 确定已知的关联字段：

  |-雇员与部门：emp.deptno=dept.deptno

  |-雇员与领导：emp.mgr=memp.empno

第一步：找到SCOTT所做的工作

第二步：以上的查询返回的是单行单列，所以只能够在WHERE或者是HAVING中使用，那么根据现在的需求，在WHERE中使用，对所有的雇员信息进行筛选

第三步：如果不需要重复信息，那么可以删除SCOTT

第四步：部门名称只需要加入dept表即可

第五步：此时的查询不可能直接使用GROUP BY进行分组，所以需要使用子查询实现分组

第六步：找到对应的领导信息，直接使用自身关联

3.案例三

列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名、部门人数、平均工资、最高和最低工资。

• 确定要使用的数据表：

  |-emp表：员工的编号、姓名

  |-dept表：部门名称

  |-emp表：领导姓名

  |-emp表：统计信息

• 确定已知的关联字段

  |-雇员与部门：emp.deptno=dept.deptno

  |-雇员与领导：emp.mgr=memp.empno

第一步：知道“SMITH”或“ALLEN”，这个查询返回多行单列

第二步：使用ANY进行筛选，并且去掉SMITH和ALLEN

第三步：找部门名称

第四步：找领导信息

第五步：部门人数，平均工资、最高及最低工资。整个查询不能使用GROUP BY，利用子查询实现统计操作