Oracle學習（7）：集合運算

集合運算

小例子

SQL> --查詢部門號10和20的員工
SQL> --1. select * from emp where deptno=10 or deptno=20;
SQL> --2. select * from emp where deptno in (10,20);
SQL> --3.集合運算（要將上述兩語句執行結果合並起來）
SQL> select * from emp where deptno=10
2 union
3 select * from emp where 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> 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> 集合運算：
SQL> 1. 參與運算的各個集合，必須列數相同，且類型一致
SQL> 2. 最終結果采用第一個集合的表頭作為表頭
SQL> 3. order by必須在每個集合後使用相同的order by

SQL> */

對於字符添加空列，使用to_char(null)

對於數字添加空列，使用to_number(null)

 

 

union

UNION運算符返回兩個集合去掉重復元素後的所有記錄 UNIONALL返回兩個集合的所有記錄，包括重復的

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
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
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 ANALYST 6000
CLERK 1900
MANAGER 2975
10875



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

30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400


29025

已選擇13行。

SQL> break on null
SQL> /


DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600


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

已選擇13行。


SQL> host cls

打開SQL執行的時間

SQL> --打開SQL執行的時間

SQL> set timing on
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行。


已用時間: 00: 00: 00.00

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
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600


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

已選擇13行。


已用時間: 00: 00: 00.00

SQL> set timing off

這兒由於數據量太少導致時間太短無法測出，在平時可以使用此種方法來檢測哪一個sql語句執行時間更短，則那個sql更為好

 

INTERSECT語句

INTERSECT 運算符返回同時屬於兩個集合的記錄

顯示薪水同時位於級別1（700~1300）和級別2（1201~1400）的員工信息：

SQL> select ename,sal from emp

2 where sal between 700 and 1300
3 INTERSECT
4 select ename,sal from emp
5 where sal between 1201 and 1400;


ENAME SAL
---------- ----------
MARTIN 1250
MILLER 1300
WARD 1250

minus

MINUS返回屬於第一個集合，但不屬於第二個集合的記錄。

顯示薪水同時位於級別1（700~1300），但不屬於級別2（1201~1400）的員工信息：

SQL> select ename,sal from emp 2 where sal between 700 and 1300
3 minus
4 select ename,sal from emp
5 where sal between 1201 and 1400;


ENAME SAL
---------- ----------
ADAMS 1100
JAMES 950
SMITH 800

集合運算注意事項

lselect語句中參數類型和個數要一致。 l可以使用括號改變集合執行的順序 l如果有order by子句，必須放到每一句查詢語句後 l集合運算采用第一個語句的表頭作為表頭