08 Oracle笔记(八) 复杂查询及总结.docxVIP

 HYPERLINK /mchina/archive/2012/09/09/2653417.html Oracle笔记（八） 复杂查询及总结 一、复杂查询 1、 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 1、确定所需要的数据表： emp表：可以查询出员工的数量； dept表：部门名称； emp表：统计信息； 2、确定已知的关联字段： emp.deptno=dept.deptno；　　 第一步：找出至少有一个员工的部门编号 SELECT deptno,COUNT(empno)FROM empGROUP BY deptnoHAVING COUNT(empno)1; 第二步：找到部门名称，肯定使用部门表，因为现在的数据量较小，所以可以将之前的emp表和dept表两个进行连接，统一采用多字段分组的方式查询； SELECT d.deptno,d.dname,COUNT(e.empno)FROM emp e,dept dWHERE e.deptno=d.deptno(+)GROUP BY d.deptno,d.dnameHAVING COUNT(e.empno)1; 第三步：依然需要继续统计 SELECT d.deptno,d.dname,COUNT(e.empno),AVG(sal),MIN(sal),MAX(sal)FROM emp e,dept dWHERE e.deptno=d.deptno(+)GROUP BY d.deptno,d.dnameHAVING COUNT(e.empno)1; 2、 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。 1、确定所需要的数据表： emp表：查询出“SMITH”或“ALLEN”工资； emp表：最终的显示需要编号、姓名； emp表：领导的姓名，自身关联； dept表：部门名称； 2、确定已知的关联字段： 雇员和领导：emp.mgr=memp.empno； 雇员和部门：emp.deptno=dept.deptno； 第一步：找出“SMITH”或“ALLEN”的工资 SELECT sal FROM emp WHERE ename IN(SMITH,ALLEN); 第二步：以上的查询返回的多行单列的记录，按照子查询的要求在WHERE子句中写合适，所以这个时候将上面的查询作为一个子查询出现，继续查询符合此要求的员工的编号、姓名。 INCLUDEPICTURE \d /images/copycode.gif \* MERGEFORMATINET  SELECT e.empno,e.enameFROM emp eWHERE e.salALL( SELECT sal FROM emp WHERE ename IN(SMITH,ALLEN)); INCLUDEPICTURE \d /images/copycode.gif \* MERGEFORMATINET  第三步：查询出部门的名称，引入部门表，同时增加消除笛卡尔积的条件 INCLUDEPICTURE \d /images/copycode.gif \* MERGEFORMATINET  SELECT e.empno,e.ename,d.dnameFROM emp e,dept dWHERE e.salALL( SELECT sal FROM emp WHERE ename IN(SMITH,ALLEN)) AND e.deptno=d.deptno; INCLUDEPICTURE \d /images/copycode.gif \* MERGEFORMATINET  第四步：领导的信息需要emp表自身关联 INCLUDEPICTURE \d /images/copycode.gif \* MERGEFORMATINET  SELECT e.empno,e.ename,d.dname,m.enameFROM emp e,dept d,emp mWHERE e.salALL( SELECT sal FROM emp WHERE ename IN(SMITH,ALLEN)) AND e.deptno=d.deptno AND e.mgr=m.empno(+); INCLUDEPICTURE \d /images/copycode.gif \* MERGEFORMATINET  3、 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 1、确定所需要的数据表： emp表：员工的编号、姓名； emp表：