复杂查询案例,郑欣,范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。,确定要使用的数据表： Emp表：员工姓名和薪金 Dept表：部门名称 Emp表：统计部门人数 确定已知的关联字段： 员工 与部门：emp.deptno=dept.deptno,范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。,第一步：查询30部门所有雇员的薪金 SELECT sal FROM EMP WHERE deptno=30;第二步：以上查询中返回的是多行单列数据，可以使用三种判断符：IN、ANY、ALL。根据要求发现找到所有员工，使用“ALL” SELECT e.ename,e.sal FROM emp e WHERE e.sal ALL(SELECT sal FROM emp WHERE deptno=30);,范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。,第三步：查询部门信息，在FROM子句之后引入dept表，再消除笛卡尔积。 SELECT e.ename,e.sal FROM emp e,dept d WHERE e.sal ALL(SELECT sal FROM emp WHERE deptno=30)AND e.deptno=d.deptno;,范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。,第四步：统计部门人数信息【分析 】进行部门人数统计，必须使用部门分组使用分组时，SELECT子句只能出现分组字段和分组函数此时 出现矛盾，因为SELECT子句中有其它字段，所以不能直接使用GROUP BY分组，可以考虑利用子查询分组，即：在FROM子查询进行分组统计，再将临时表采用多表查询。,SELECT e.ename,e.sal,t.count FROM emp e,dept d,(SELECT deptno dno,count(empno) countFROM empGROUP BY deptno) t WHERE e.sal ALL(SELECT sal FROM emp WHERE deptno=30)AND e.deptno=d.deptnoAND d.deptno=t.dno;,范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。,范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。,确定要使用的数据表： Emp表：员工信息 Dept表：部门名称 Emp表：领导信息 确定已知的关联字段： 雇员与部门：emp.deptno=dept.deptno 雇员与领导：emp.mgr=memp.empno,范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。,第一步：没有SCOTT的工作就无法知道哪个雇员满足条件，因此先找到SCOTT的工作。 SELECT job FROM emp WHERE ename=SCOTT;第二步：以上查询返回的是单行单列，所以只能在WHERE或者HAVING中使用，根据需要在WHERE中使用，对所有雇员信息进行筛选。 SELECT e.empno,e.ename,e.job FROM emp e WHERE job=(SELECT job FROM emp WHERE ename=SCOTT);,范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。,第三步，如果不需要重复信息，可以清除“SCOTT” SELECT e.empno,e.ename,e.job FROM emp e WHERE job=(SELECT job FROM emp WHERE ename=SCOTT)AND enameSCOTT;,范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。,第四步：部门名称只需要加入dept表即可 SELECT e.empno,e.ename,e.job FROM emp e,dept d WHERE job=(SELECT job FROM emp WHERE ename=SCOTT)AND enameSCOTTAND e.deptno=d.deptno;,范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。,第五步：此时不可能直接使用GROUP BY进行分组，所以需要使用子查询实现分组 SELECT e.empno,e.ename,e.job,temp.count FROM emp e,dept d,(SELECT deptno dno,COUNT(empno) countFROM empGROUP BY deptno) temp WHERE job=(SELECT job FROM emp WHERE ename=SCOTT)AND enameSCOTTAND e.deptno=d.deptnoAND d.deptno=temp.dno;,范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。,第六步：找到对应的领导信息，直接使用自身关联 SELECT e.empno,e.ename,e.job,temp.count,m.ename FROM emp e,dept d,(SELECT deptno dno,COUNT(empno) countFROM empGROUP BY deptno) temp,emp m WHERE e.job=(SELECT job FROM emp WHERE ename=SCOTT)AND e.enameSCOTTAND e.deptno=d.deptnoAND d.deptno=temp.dnoAND e.mgr=m.empno;,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,确定要使用的数据表： Emp表：员工编号、姓名 Dept表：部门名称 Emp表：领导姓名 Emp表：统计信息 确定已知的关联字段： 雇员与部门：emp.deptno=dept.deptno 雇员与领导：emp.mgr=memp.empno,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,第一步：知道“SMITH”或“ALLEN”，这个查询返回多行单列（WHERE中使用） SELECT sal FROM emp WHERE ename IN (SMITH, ALLEN);,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,第二步：现在应该比里面的任意一个多即可，但是要去掉两个雇员。 SELECT e.empno,e.ename,e.sal FROM emp e WHERE e.salANY(SELECT salFROM empWHERE ename IN (SMITH, ALLEN)AND e.ename NOT IN (SMITH,ALLEN);,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,第三步：找到部门名称 SELECT e.empno,e.ename,e.sal FROM emp e,dept d WHERE e.salANY(SELECT salFROM empWHERE ename IN (SMITH, ALLEN)AND e.ename NOT IN (SMITH,ALLEN)AND e.deptno=d.deptno;,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,第四步：找到领导信息 SELECT e.empno,e.ename,e.sal,m.ename FROM emp e,dept d,emp m WHERE e.salANY(SELECT salFROM empWHERE ename IN (SMITH, ALLEN)AND e.ename NOT IN (SMITH,ALLEN)AND e.deptno=d.deptnoAND e.mgr=m.empno(+);,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,第五步：统计部门人数、平均工资、最高及最低工资。整个查询里面不能够直接使用GROUP BY，所以现在应该利用子查询实现统计操作。完整代码如下：,范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号 、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资,SELECT e.empno,e.ename,e.sal,m.ename,t.count,t.avg,t.max,t.min FROM emp e,dept d,emp m,(SELECT deptno dno,COUNT(empno) count,AVG(sal) avg,MAX(sal) max,MIN(sal) minFROM empGROUP BY deptno) t WHERE e.salANY(SELECT salFROM empWHERE ename IN (SMITH, ALLEN)AND e.ename NOT IN (SMITH,ALLEN)AND e.deptno=d.deptnoAND e.mgr=m.empno(+)AND d.deptno=t.dno;,范例四：列出受雇日期早于其直接上级的所有员 工的编号 、姓名、部门名称、部门位置、部门人数。,确定要使用的数据表： Emp表：员工编号 、姓名 Dept表：部门名称、部门位置 Emp表：人数 Emp表：领导 确定已知的关联字段： 雇员与领导：emp.mgr=memp.empno 雇员与部门：emp.deptno=dept.deptno,范例四：列出受雇日期早于其直接上级的所有员 工的编号 、姓名、部门名称、部门位置、部门人数。,第一步：emp表进行自身关联除了设置消除笛卡尔积外，还要判断受雇日期。SELECT e.empno,e.ename FROM emp e,emp m WHERE e.mgr=m.empno(+) AND e.hiredatem.hiredate,范例四：列出受雇日期早于其直接上级的所有员 工的编号 、姓名、部门名称、部门位置、部门人数。,第二步：找到部门信息SELECT e.empno,e.ename,d.dname,d.loc FROM emp e,emp m,dept d WHERE e.mgr=m.empno(+) AND e.hiredatem.hiredateAND e.deptno=d.deptno;,范例四：列出受雇日期早于其直接上级的所有员 工的编号 、姓名、部门名称、部门位置、部门人数。,第三步：统计部门人数 SELECT e.empno,e.ename,d.dname,d.loc,t.count FROM emp e,emp m,dept d,(SELECT deptno dno,COUNT(empno) countFROM empGROUP BY deptno) t WHERE e.mgr=m.empno(+) AND e.hiredatem.hiredateAND e.deptno=d.deptnoAND d.deptno=t.dno;,