Oracle集合运算

sql> --什么是集合运算?

sql> --查询10号部门和20号部门的员工

sql> --第一种

sql> select *

  2  from emp

  3  where deptno=10 or deptno=20;

     empno ename      job              mgr hiredate         sal       comm     deptno                                  

---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                  

      7369 smith      clerk           7902 17-12月-80       800                    20                                  

      7566 jones      manager         7839 02-4月 -81      2975                    20                                  

      7782 clark      manager         7839 09-6月 -81      2450                    10                                  

      7788 scott      analyst         7566 13-7月 -87      3000                    20                                  

      7839 king       president            17-11月-81      5000                    10                                  

      7876 adams      clerk           7788 13-7月 -87      1100                    20                                  

      7902 ford       analyst         7566 03-12月-81      3000                    20                                  

      7934 miller     clerk           7782 23-1月 -82      1300                    10                                  

已选择8行。

sql> --第二种

  3  where deptno in (10,20);

sql> --第三种:集合运算

sql> -- select * from emp where deptno=10;

sql> -- select * from emp where deptno=20;

sql> -- 集合的三种运算:并，交，差

sql> select * from emp where deptno=10

  2  union

  3  select * from emp where deptno=20;

sql> --关于并集，讨论性能

sql> --group by的增强

sql> select deptno,job,sum(sal)

  3  group by rollup(deptno,job);

    deptno job         sum(sal)                                                                                        

---------- --------- ----------                                                                                        

        10 clerk           1300                                                                                        

        10 manager         2450                                                                                        

        10 president       5000                                                                                        

        10                 8750                                                                                        

        20 clerk           1900                                                                                        

        20 analyst         6000                                                                                        

        20 manager         2975                                                                                        

        20                10875                                                                                        

        30 clerk            950                                                                                        

        30 manager         2850                                                                                        

        30 salesman        5600                                                                                        

        30                 9400                                                                                        

                          29025                                                                                        

已选择13行。

sql> break on deptno skip 2

sql> /

           manager         2450                                                                                        

           president       5000                                                                                        

                           8750                                                                                        

           analyst         6000                                                                                        

           manager         2975                                                                                        

                          10875                                                                                        

           manager         2850                                                                                        

           salesman        5600                                                                                        

                           9400                                                                                        

sql> host cls

sql> select deptno,job,sum(sal) from emp group by deptno,job

  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: 查询块具有不正确的结果列数

sql> --使用集合操作的 注意事项:

sql> --1.  参与运算的集合必须列数相同

sql> --2.  运算后的结果的表头为第一个集合的表头

  3  select deptno,to_char(null),sum(sal) from emp group by deptno

  5  select to_number(null),to_char(null),sum(sal) from emp;

           clerk           1900                                                                                        

sql> --打开sql运行时间开关

sql> set timing on

已用时间:  00: 00: 00.56

已用时间:  00: 00: 00.50

sql> set timing off

sql> break on null

sql> --交集:

sql> -- 查询工资属于1000~2000这个区间和1500~2500这个区间

sql> select * from emp where sal between 1000 and 2000

  2  intersect

  3  select * from emp where sal between 1500 and 2500;

      7499 allen      salesman        7698 20-2月 -81      1600        300         30                                  

      7844 turner     salesman        7698 08-9月 -81      1500          0         30                                  

sql> --差集：

sql> --查询工资属于1000~2000,但不属于1500~2500

sql> ed

已写入 file afiedt.buf

  1  select * from emp where sal between 1000 and 2000

  2  minus

  3* select * from emp where sal between 1500 and 2500

      7521 ward       salesman        7698 22-2月 -81      1250        500         30                                  

      7654 martin     salesman        7698 28-9月 -81      1250       1400         30                                  

sql> --1.列数相同

sql> --2. 采用第一个集合的表头

sql> spool off