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

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

[sql] view plain copy

  1. create table EMP(
  2.     EMPNO NUMBER,
  3.     ENAME VARCHAR2(10),
  4.     JOB VARCHAR2(9),
  5.     MGR NUMBER,
  6.     HIREDATE DATE,
  7.     SAL BINARY_DOUBLE,
  8.     COMM BINARY_DOUBLE,
  9.     DEPTNO NUMBER
  10. );

其中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

部门表

[sql] view plain copy

  1. create table dept(
  2.     DEPTNO NUMBER,
  3.     DNAME VARCHAR2(50)
  4. );

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自带的一个数据,用来统计取出的数据是第几条。

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

[sql] view plain copy

  1. select rownum,empno,ename,sal from emp
  2. where rownum<=3
  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

例如

[sql] view plain copy

  1. 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
排序后

[sql] view plain copy

  1. select rownum,empno,ename,sal from emp
  2. 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的数据进行排序。

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

[sql] view plain copy

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

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

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

[sql] view plain copy

  1. select * from(
  2. select a.empno,a.ename,a.sal,
  3. ((select SUM(b.sal) from  emp b where b.deptno=a.deptno)
  4. /(select COUNT(*) from emp c where c.deptno=a.deptno)) as avgsal
  5. from emp a) employ
  6. where employ.sal>employ.avgsal
  7. 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里面写取数据效率就会更高,避免外面再嵌套查
询语句:

[sql] view plain copy

  1. select empno,ename,sal,avgsal
  2. from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
  3. 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年的员工:

[sql] view plain copy

  1. select empno,ename,HIREDATE from emp
  2. where HIREDATE>=to_date(’01-1月 -16′)
  3. and HIREDATE<=to_date(’30-12月 -16′);

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

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

[sql] view plain copy

  1. select
  2. (select COUNT(*) from emp
  3. where HIREDATE>=to_date(’01-1月 -11′)
  4. and HIREDATE<=to_date(’30-12月 -16′)) as Total,
  5. (select COUNT(*) from emp
  6. where HIREDATE>=to_date(’01-1月 -16′)
  7. and HIREDATE<=to_date(’30-12月 -16′)) as “2016”,
  8. (select COUNT(*) from emp
  9. where HIREDATE>=to_date(’01-1月 -15′)
  10. and HIREDATE<=to_date(’30-12月 -15′)) as “2015”,
  11. (select COUNT(*) from emp
  12. where HIREDATE>=to_date(’01-1月 -14′)
  13. and HIREDATE<=to_date(’30-12月 -14′)) as “2014”,
  14. (select COUNT(*) from emp
  15. where HIREDATE>=to_date(’01-1月 -13′)
  16. and HIREDATE<=to_date(’30-12月 -13′)) as “2013”,
  17. (select COUNT(*) from emp
  18. where HIREDATE>=to_date(’01-1月 -12′)
  19. and HIREDATE<=to_date(’30-12月 -12′)) as “2012”,
  20. (select COUNT(*) from emp
  21. where HIREDATE>=to_date(’01-1月 -11′)
  22. and HIREDATE<=to_date(’30-12月 -11′)) as “2011”
  23. from dual;

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

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

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

未经允许不得转载:JX BLOG » 【oracle学习】3.子查询和集合运算练习

赞 (0)

评论 0

评论前必须登录!

登陆 注册