Oracle集合运算

来源：互联网发布：南京秦淮网络问政编辑：程序博客网时间：2024/05/16 12:14

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> --第二种
SQL> select *
2 from emp
3 where deptno in (10,20);

已选择8行。

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;

已选择8行。

SQL> --关于并集，讨论性能
SQL> --group by的增强
SQL> select deptno,job,sum(sal)
2 from emp
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

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        30                 9400
                          29025

已选择13行。

SQL> break on deptno skip 2
SQL> /

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
           MANAGER         2450
           PRESIDENT       5000
                           8750


        20 CLERK           1900
           ANALYST         6000
           MANAGER         2975
                          10875

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------

        30 CLERK            950
           MANAGER         2850
           SALESMAN        5600
                           9400


                          29025

已选择13行。

SQL> host cls

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

SQL> --使用集合操作的注意事项:
SQL> --1. 参与运算的集合必须列数相同
SQL> --2. 运算后的结果的表头为第一个集合的表头
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)
---------- --------- ----------
        10 CLERK           1300
           MANAGER         2450
           PRESIDENT       5000
                           8750


        20 ANALYST         6000
           CLERK           1900
           MANAGER         2975
                          10875

已选择13行。

SQL> --打开sql运行时间开关
SQL> set timing on
SQL> /

已选择13行。

已用时间: 00: 00: 00.56
SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job);

已选择13行。

已用时间: 00: 00: 00.50
SQL> set timing off
SQL> break on null
SQL> host cls

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;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      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
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10

SQL> --1.列数相同
SQL> --2. 采用第一个集合的表头
SQL> spool off