主页 > 知识库 > 数据库 > Oracle >

Oracle笔记:复杂查询及总结

来源:中国IT实验室 作者:佚名 发表于:2012-09-17 11:29  点击:
一、复杂查询 1、 列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。 1、确定所需要的数据表: ?emp表:可以查询出员工的数量; ?dept表:部门名称; ?emp表:统计信息; 2、确定已知的关联字段: ?emp.deptno=dep
  一、复杂查询
   
    1、 列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。
   
    1、确定所需要的数据表:
   
    ?emp表:可以查询出员工的数量;
   
    ?dept表:部门名称;
   
    ?emp表:统计信息;
   
    2、确定已知的关联字段:
   
    ?emp.deptno=dept.deptno;
   
    第一步:找出至少有一个员工的部门编号
   
    SELECT deptno,COUNT(empno)
   
    FROM emp
   
    GROUP BY deptno
   
    HAVING COUNT(empno)>1;第二步:找到部门名称,肯定使用部门表,因为现在的数据量较小,所以可以将之前的emp表和dept表两个进行连接,统一采用多字段分组的方式查询;
   
    SELECT d.deptno,d.dname,COUNT(e.empno)
   
    FROM emp e,dept d
   
    WHERE e.deptno=d.deptno(+)
   
    GROUP BY d.deptno,d.dname
   
    HAVING COUNT(e.empno)>1;第三步:依然需要继续统计
   
    SELECT d.deptno,d.dname,COUNT(e.empno),AVG(sal),MIN(sal),MAX(sal)
   
    FROM emp e,dept d
   
    WHERE e.deptno=d.deptno(+)
   
    GROUP BY d.deptno,d.dname
   
    HAVING 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子句中写合适,所以这个时候将上面的查 询作为一个子查询出现,继续查询符合此要求的员工的编号、姓名。
   
    SELECT e.empno,e.ename
   
    FROM emp e
   
    WHERE e.sal>ALL(
   
    SELECT sal
   
    FROM emp
   
    WHERE ename IN(‘SMITH','ALLEN’));第三步:查询出部门的名称,引入部门表,同时增加消除笛卡尔积的条件
   
    SELECT e.empno,e.ename,d.dname
   
    FROM emp e,dept d
   
    WHERE e.sal>ALL(
   
    SELECT sal
   
    FROM emp
   
    WHERE ename IN(‘SMITH','ALLEN’))
   
    AND e.deptno=d.deptno;第四步:领导的信息需要emp表自身关联
   
    SELECT e.empno,e.ename,d.dname,m.ename
   
    FROM emp e,dept d,emp m
   
    WHERE e.sal>ALL(
   
    SELECT sal
   
    FROM emp
   
    WHERE ename IN(‘SMITH','ALLEN’))
   
    AND e.deptno=d.deptno
   
    AND e.mgr=m.empno(+);3、 列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列。
   
    1、确定所需要的数据表:
   
    ?emp表:员工的编号、姓名;
   
    ?emp表:领导的编号、姓名、计算年薪;
   
    2、确定已知的关联字段:emp.mgr=memp.empno;
   
    SELECT e.empno,e.ename,m.empno,m.ename,(m.sal+NVL(m.comm,0))*12 income
   
    FROM emp e,emp m
   
    WHERE e.mgr=m.empno(+)
   
    ORDER BY income DESC;4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
   
    1、确定所需要的数据表:
   
    ?emp表:雇员的编号、姓名;
   
    ?emp表:求出领导的工作日期;
   
    ?dept表:部门名称、位置;
   
    ?emp表:统计部门人数;
   
    2、确定已知的关联字段:
   
    ?雇员和部门:emp.deptno=dept.deptno;
   
    ?雇员和领导:emp.mgr=memp.empno;
   
    第一步:列出受雇日期早于其直接上级的所有员工的编号、姓名 -- 自身关联emp表。
   
    SELECT e.empno,e.ename
   
    FROM emp e,emp m
   
    WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate;第二步:加入部门信息,继续引入部门表
   
    SELECT e.empno,e.ename,d.dname,d.loc
   
    FROM emp e,emp m,dept d
   
    WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate
   
    AND e.deptno=d.deptno;额外提问,此时的笛卡尔积:emp表的14条 * emp表的14条 * dept表的4条;
   
    第三步:统计部门人数,此时由于要使用统计函数,而且以上的查询也无法再直接出现统计函数,所以使用子查询完成
   
    SELECT e.empno,e.ename,d.dname,d.loc,temp.count
   
    FROM emp e,emp m,dept d,(
   
    SELECT deptno dno,COUNT(empno) count
   
    FROM emp
   
    GROUP BY deptno) temp
   
    WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate
   
    AND e.deptno=d.deptno
   
    AND e.deptno=temp.dno;当查询显示的时候需要统计信息,但是又不能直接使用统计函数查询的话,通过子查询在FROM子句之后进行统计。
   
    5、列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。
   
    1、确定所需要的数据表:
   
    ?dept表:部门的信息;
   
    ?emp表:求出所有的统计信息;
   
    2、确定已知的关联字段:emp.deptno=dept.deptno;
   
    SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
   
    FROM emp e,dept d
   
    WHERE e.deptno(+)=d.deptno

有帮助
(0)
0%
没帮助
(1)
100%