【oracle学习】3.子查询和集合运算练习

最新推荐文章于 2021-04-04 03:08:33 发布

光仔December

最新推荐文章于 2021-04-04 03:08:33 发布

阅读量1.2k

点赞数

分类专栏： SQL Oracle基础与提高

本文链接：https://blog.csdn.net/acmman/article/details/52343811

版权

SQL 同时被 2 个专栏收录

16 篇文章 1 订阅

订阅专栏

Oracle基础与提高

12 篇文章 26 订阅

订阅专栏

首先介绍一下我们要用到的两张表以及数据：
员工信息表

create table EMP(
    EMPNO NUMBER,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER,
    HIREDATE DATE,
    SAL BINARY_DOUBLE,
    COMM BINARY_DOUBLE,
    DEPTNO NUMBER
);

其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。

SQL> select * from emp;
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
           1114 李磊磊               会计                          1110 22-12月-15      2500     0          50
           1115 张少丽               销售                          1110 11-3月 -16      2400 1400          30
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20
           1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
           1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50

部门表

create table dept(
    DEPTNO NUMBER,
    DNAME VARCHAR2(50)
);

SQL> select * from dept t;

DEPTNO    DNAME
-------- --------
20        管理部门
30        销售部门
40        后勤部门
50        金融部门

练习写出以下sql

分析
(1)找出员工表中工资最高的前三名
效果图中的行号rownum其实是一个伪列，我们可以取出来看一下:
select rownum,empno,ename,sal from emp;

    ROWNUM      EMPNO ENAME                  SAL
---------- ---------- -------------------- -----
         1       1110 张三                  5200
         2       1111 李四                  3400
         3       1112 王五                  4400
         4       1113 赵二                  3450
         5       1114 李磊磊                2500
         6       1115 张少丽                2400
         7       1116 林建国                5700
         8       1117 马富邦                2800
         9       1118 沈倩                  2100

这是oracle自带的一个数据，用来统计取出的数据是第几条。

这时，大家可能会这么想着取出薪水最高的前三名:

select rownum,empno,ename,sal from emp
where rownum<=3
order by sal desc;

结果:
    ROWNUM      EMPNO ENAME                  SAL
---------- ---------- -------------------- -----
         1       1110 张三                  5200
         3       1112 王五                  4400
         2       1111 李四                  3400
这是最高的前三吗？不是，应该是
         7       1116 林建国                5700
         1       1110 张三                  5200
         3       1112 王五                  4400

为什么不对呢？我们看一下rownum的使用规则:
注意的问题：
1. 行号永远按照默认的顺序生成
2. 行号只能使用< <=,不能使用> >=
因为行号是一个一个取出数据时遍历的，不能没有1、2就直接>=3

例如

select rownum,empno,ename,sal from emp;

    ROWNUM      EMPNO ENAME                  SAL
---------- ---------- -------------------- -----
         1       1110 张三                  5200
         2       1111 李四                  3400
         3       1112 王五                  4400
         4       1113 赵二                  3450
         5       1114 李磊磊                2500
         6       1115 张少丽                2400
         7       1116 林建国                5700
         8       1117 马富邦                2800
         9       1118 沈倩                  2100
排序后

select rownum,empno,ename,sal from emp
order by sal desc;

    ROWNUM      EMPNO ENAME                  SAL
---------- ---------- -------------------- -----
         7       1116 林建国                5700
         1       1110 张三                  5200
         3       1112 王五                  4400
         4       1113 赵二                  3450
         2       1111 李四                  3400
         8       1117 马富邦                2800
         5       1114 李磊磊                2500
         6       1115 张少丽                2400
         9       1118 沈倩                  2100

你会发现，排不排序，rownum都是按照之前默认的生成来显示的。

上个例子是因为先取出了数据，行号已经生成，然后再进行排序，最后的结果只能是对得到的行号为1/2/3的数据进行排序。

我们修改为以下语句就可以实现排序(先排序，后生成行号取出):

select rownum,empno,ename,sal from
(select * from emp order by sal desc)
where rownum<=3;

    ROWNUM      EMPNO ENAME                  SAL
---------- ---------- -------------------- -----
         1       1116 林建国                5700
         2       1110 张三                  5200
         3       1112 王五                  4400

(2)找到员工表中薪水大于本部门平均薪水的员工。

select * from(
select a.empno,a.ename,a.sal,
((select SUM(b.sal) from  emp b where b.deptno=a.deptno)
/(select COUNT(*) from emp c where c.deptno=a.deptno)) as avgsal
from emp a) employ
where employ.sal>employ.avgsal
order by employ.sal asc;

     EMPNO ENAME                  SAL     AVGSAL
---------- -------------------- ----- ----------
      1114 李磊磊                2500   2300.000
      1113 赵二                  3450   3125.000
      1112 王五                  4400   3400.000
      1116 林建国                5700   5450.000

这里的思路是，首先取出每个部门的平均薪水，平均薪水的计算方法是
avgsal=(该员工所在部门总薪水)/(该员工所在部门总人数)
然后依次取出其它数据，作为一个结果集让另外一个select去查询(相当
于一个新表employ)，然后控制条件employ.sal>employ.avgsal，找到工
资大于平均薪水的员工，最后按照薪水排序显示。

其实上面可以写的更简洁一些，因为有一个函数avg是用来计算平均数的，
而我们将子查询放在from里面写取数据效率就会更高，避免外面再嵌套查
询语句:

select empno,ename,sal,avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal order by e.sal asc;

     EMPNO ENAME                  SAL     AVGSAL
---------- -------------------- ----- ----------
      1114 李磊磊                2500   2300.000
      1113 赵二                  3450   3125.000
      1112 王五                  4400   3400.000
      1116 林建国                5700   5450.000

(3)统计每年入职的员工人数
我们以16年为例，我们查询入职时间为16年的员工:

select empno,ename,HIREDATE from emp
where HIREDATE>=to_date('01-1月 -16')
and HIREDATE<=to_date('30-12月 -16');

     EMPNO ENAME                HIREDATE
---------- -------------------- --------------
      1115 张少丽               11-3月 -16
      1116 林建国               22-1月 -16

按照上述逻辑，
想统计数据库表中16-11年的各个年份的数据，sql将如下所示:

select
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -11')
and HIREDATE<=to_date('30-12月 -16')) as Total,
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -16')
and HIREDATE<=to_date('30-12月 -16')) as "2016",
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -15')
and HIREDATE<=to_date('30-12月 -15')) as "2015",
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -14')
and HIREDATE<=to_date('30-12月 -14')) as "2014",
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -13')
and HIREDATE<=to_date('30-12月 -13')) as "2013",
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -12')
and HIREDATE<=to_date('30-12月 -12')) as "2012",
(select COUNT(*) from emp
where HIREDATE>=to_date('01-1月 -11')
and HIREDATE<=to_date('30-12月 -11')) as "2011"
from dual;

TOTAL 2016 2015 2014 2013 2012 2011
---------- ---------- ---------- ---------- ---------- ---------- ----------
8 2 2 1 1 1 1

效果和要求是一样的。
但是我个人感觉这个sql写的特别冗余，需要优化。

另外一种比较好的策略就是使用“存储过程”，请查看相关总结文章，这里不进行阐述。

转载请注明出处：http://blog.csdn.net/acmman/article/details/52343811