【oracle学习】2.集合运算

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

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        金融部门

集合运算符
可以取得两个集合的并集、交集和差集


对于集合A和B(以下均是A在前B在后)
其中UNION(A U B)是取A以及B的所有信息，对于A和B的相同的数据只取一次。
其中UNION ALL(A U+ B)取A以及B的所有信息，对于A和B的相同的数据各取一次。
其中INTERSECT(A n B)只取A和B相同的部分，只取一次。
其中MINUS(A - B)只取属于A但不属于B的部分。

1.UNION（并集）
对于“查询20和30号部门的员工”，我们可以有一下方案：
(1)select * from emp where deptno in (20,30);
(2)select * from emp where deptno=20 or deptno=30;
但是使用集合查询，我们可以这么写
select * from emp where deptno=20  
 union
select * from emp where deptno=30;
          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
           1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20


利用集合运算实现group by的增强
我们这里统计
(1)每个部门每种职位工资总和
(2)每个部门所有员工工资总和
(3)所有部门所有员工工资总和
SQL> select deptno,job,sum(sal) from emp group by deptno,job
  2  union
  3  select deptno,sum(sal) from emp group by deptno
  4  union
  5  select sum(sal) from emp;
select deptno,sum(sal) from emp group by deptno
*
第 3 行出现错误:
ORA-01789: 查询块具有不正确的结果列数

以上错误提醒大家注意
1. 参与运算的各个集合必须列数相同 且类型一致
2. 采用第一个集合的表头作为最后的表头
3. 如果排序，必须在每个集合后使用相同的order by
4. 可以使用括号
select deptno,job,sum(sal) from emp group by deptno,job
 union
select deptno,to_char(null),sum(sal) from emp group by deptno
 union
select to_number(null),to_char(null),sum(sal) from emp;

         DEPTNO JOB                SUM(SAL)
--------------- ------------------ --------
             20 主管                  10900
             20                       10900
             30 销售                  10200
             30                       10200
             40 后勤                   6250
             40                        6250
             50 会计                   4600
             50                        4600
                                      31950

(2)INTERSECT（交集）
SQL> select ename,sal from emp
  2  where sal between 2100 and 3500
  3  INTERSECT
  4  select ename,sal from emp
  5  where sal between 3400 and 6000;

ENAME                  SAL
-------------------- -----
李四                  3400
赵二                  3450

(3)MINUS（差集）
SQL> select ename,sal from emp
  2  where sal between 2100 and 3500
  3  minus
  4  select ename,sal from emp
  5  where sal between 3400 and 6000;

ENAME                  SAL
-------------------- -----
张少丽                2400
李磊磊                2500
沈倩                  2100






补充:
我们评估一个sql执行的效率，就需要查询其执行的时间，我们可以通过以下方式打开sql执行的时间。
set timing on
SQL> select deptno,job,sum(sal) from emp group by deptno,job
  2   union
  3  select deptno,to_char(null),sum(sal) from emp group by deptno
  4   union
  5  select to_number(null),to_char(null),sum(sal) from emp;

         DEPTNO JOB                SUM(SAL)
--------------- ------------------ --------
             20 主管                  10900
             20                       10900
             30 销售                  10200
             30                       10200
             40 后勤                   6250
             40                        6250
             50 会计                   4600
             50                        4600
                                      31950

已选择9行。

已用时间:  00: 00: 00.02
集合运算，集合越多，花的时间越多。

关闭sql执行时间

set timing off

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