11 Oracle基础 - 复杂查询案例

复杂查询案例范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。确定要使用的数据表：Emp表：员工姓名和薪金Dept表：部门名称Emp表：统计部门人数确定已知的关联字段：员工与部门：emp.deptno=dept.deptno范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。第一步：查询30部门所有雇员的薪金SELECTsalFROMEMPWHEREdeptno=30;第二步：以上查询中返回的是多行单列数据，可以使用三种判断符：IN、ANY、ALL。根据要求发现找到所有员工，使用“>ALL”SELECTe.ename,e.salFROMempeWHEREe.sal>ALL(SELECTsalFROMempWHEREdeptno=30);范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。第三步：查询部门信息，在FROM子句之后引入dept表，再消除笛卡尔积。SELECTe.ename,e.salFROMempe,deptdWHEREe.sal>ALL(SELECTsalFROMempWHEREdeptno=30)ANDe.deptno=d.deptno;范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。第四步：统计部门人数信息

【分析】

进行部门人数统计，必须使用部门分组

使用分组时，SELECT子句只能出现分组字段和分组函数此时出现矛盾，因为SELECT子句中有其它字段，所以不能直接使用GROUPBY分组，可以考虑利用子查询分组，即：在FROM子查询进行分组统计，再将临时表采用多表查询。SELECTe.ename,e.sal,t.countFROMempe,deptd,(SELECTdeptnodno,count(empno)countFROMempGROUPBYdeptno)tWHEREe.sal>ALL(SELECTsalFROMempWHEREdeptno=30)ANDe.deptno=d.deptnoANDd.deptno=t.dno;范例一：列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。确定要使用的数据表：Emp表：员工信息Dept表：部门名称Emp表：领导信息确定已知的关联字段：雇员与部门：emp.deptno=dept.deptno雇员与领导：emp.mgr=memp.empno范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。第一步：没有SCOTT的工作就无法知道哪个雇员满足条件，因此先找到SCOTT的工作。SELECTjobFROMempWHEREename=‘SCOTT’;第二步：以上查询返回的是单行单列，所以只能在WHERE或者HAVING中使用，根据需要在WHERE中使用，对所有雇员信息进行筛选。SELECTe.empno,e.ename,e.jobFROMempeWHEREjob=(SELECTjobFROMempWHEREename=‘SCOTT’);范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。第三步，如果不需要重复信息，可以清除“SCOTT”SELECTe.empno,e.ename,e.jobFROMempeWHEREjob=(SELECTjobFROMempWHEREename=‘SCOTT’)

ANDename<>’SCOTT’;范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。第四步：部门名称只需要加入dept表即可SELECTe.empno,e.ename,e.jobFROMempe,deptdWHEREjob=(SELECTjobFROMempWHEREename=‘SCOTT’)ANDename<>’SCOTT’

ANDe.deptno=d.deptno;范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。第五步：此时不可能直接使用GROUPBY进行分组，所以需要使用子查询实现分组SELECTe.empno,e.ename,e.job,temp.countFROMempe,deptd,(SELECTdeptnodno,COUNT(empno)countFROMempGROUPBYdeptno)tempWHEREjob=(SELECTjobFROMempWHEREename=‘SCOTT’)ANDename<>’SCOTT’ANDe.deptno=d.deptnoANDd.deptno=temp.dno;范例二：列出与”SCOTT”从事相同工作的所有员工及部门名称、部门人数、领导姓名。第六步：找到对应的领导信息，直接使用自身关联SELECTe.empno,e.ename,e.job,temp.count,m.enameFROMempe,deptd,(SELECTdeptnodno,COUNT(empno)countFROMempGROUPBYdeptno)temp,empmWHEREe.job=(SELECTjobFROMempWHEREename='SCOTT')ANDe.ename<>'SCOTT'ANDe.deptno=d.deptnoANDd.deptno=temp.dno

ANDe.mgr=m.empno;范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资确定要使用的数据表：Emp表：员工编号、姓名Dept表：部门名称Emp表：领导姓名Emp表：统计信息确定已知的关联字段：雇员与部门：emp.deptno=dept.deptno雇员与领导：emp.mgr=memp.empno范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资第一步：知道“SMITH”或“ALLEN”，这个查询返回多行单列（WHERE中使用）SELECTsalFROMempWHEREenameIN(‘SMITH’,‘ALLEN’);范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资第二步：现在应该比里面的任意一个多即可，但是要去掉两个雇员。SELECTe.empno,e.ename,e.salFROMempeWHEREe.sal>ANY(SELECTsalFROMempWHEREenameIN(‘SMITH’,‘ALLEN’))ANDe.enameNOTIN(‘’SMITH,’ALLEN’);范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资第三步：找到部门名称SELECTe.empno,e.ename,e.salFROMempe,deptdWHEREe.sal>ANY(SELECTsalFROMempWHEREenameIN(‘SMITH’,‘ALLEN’))ANDe.enameNOTIN(‘’SMITH,’ALLEN’)ANDe.deptno=d.deptno;范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资第四步：找到领导信息SELECTe.empno,e.ename,e.sal,m.enameFROMempe,deptd,empmWHEREe.sal>ANY(SELECTsalFROMempWHEREenameIN(‘SMITH’,‘ALLEN’))ANDe.enameNOTIN(‘’SMITH,’ALLEN’)ANDe.deptno=d.deptnoANDe.mgr=m.empno(+);范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资第五步：统计部门人数、平均工资、最高及最低工资。整个查询里面不能够直接使用GROUPBY，所以现在应该利用子查询实现统计操作。完整代码如下：范例三：列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低工资SELECTe.empno,e.ename,e.sal,m.ename,t.count,t.avg,t.max,t.minFROMempe,deptd,empm,(SELECTdeptnodno,COUNT(empno)count,AVG(sal)avg,MAX(sal)max,MIN(sal)minFROMempGROUPBYdeptno)tWHEREe.sal>ANY(SELECTsalFROMempWHEREenameIN(‘SMITH’,‘ALLEN’))ANDe.enameNOTIN(‘’SMITH,’ALLEN’)ANDe.deptno=d.deptnoANDe.mgr=m.empno(+)ANDd.deptno=t.dno;范例四：列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。确定要使用的数据表：Emp表：员工编号、姓名Dept表：部门名称、部门位置Emp表：人数Emp表：领导确定已知的关联字段：雇员与领导：emp.mgr=memp.empno雇员与部门：emp.deptno=dept.deptno范例四：列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。第一步：emp表进行自身关联除了设置消除笛卡尔积外，还要判断受雇日期。SELECTe.empno,e.enameFROMempe,empmWHEREe.mgr=m.empno(+)ANDe.hiredate<m.hiredate范例四：列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。第二步：找到部门信息SELECTe.empno,e.ename,d.dname,d.locFROMempe,empm,deptdWHEREe.mgr=m.empno(+)ANDe.hiredate<m.hiredateANDe.deptno=d.deptno;范例四：列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。第三步：统计部门人数SELECTe.empno,e.ename,d.dname,d.loc,t.countFROMempe,empm,deptd,( SELECTdeptnodno,COUNT(empno)countFROMempGROUPBYdeptno)tWHEREe.mgr=m.empno(+)ANDe.hiredate<m.hiredateANDe.deptno=d.deptnoANDd.deptno=t.dno;范例五：列出所有“CLERK”的姓名和部门名称、部门人数、工资等级。第一步，查询所有办事员的信息SELECTe.enameFROMempeWHEREe.job=‘CLERK’范例五：列出所有“CLERK”的姓名和部门名称、部门人数、工资等级。第二步，查询部门名称SELECTe.ename,d.dnameFROMempe,deptdWHEREe.job=‘CLERK’