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

首先介绍一下我们要用到的两张表以及数据:
员工信息表
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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

光仔December

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值