1.知識點：可以對照下面的錄屏進行閱讀
SQL> -- 查詢10和20號部門的員工的3種方法 SQL> --1. select * from emp where deptno in (10,20); SQL> --2. select * from emp where deptno=10 or deptno=20; SQL> --3. 集合運算：union/union all並集；intersect 交集；minus差集 SQL> -- select * from emp where deptno=10 SQL> -- + SQL> -- select * from emp where deptno=20; SQL> --union：並集，重復的區域只取一次；union all：並集，重復的區域取兩次 SQL> select * from emp where deptno=10 2 union 3 select * from emp where deptno=20; SQL> --利用集合運算實現group by的增強 SQL> --注意 SQL> --1. 參與運算的各個集合必須列數相同 且類型一致 SQL> --2. 采用第一個集合的表頭作為最後的表頭 SQL> --3. 如果排序，必須在每個集合後使用相同的order by SQL> --4. 可以使用括號 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; SQL> --打開sql執行時間，每次執行sql語句都會顯示執行時間 SQL> set timing on SQL> --關閉sql執行時間 SQL> set timing off SQL> --交集：既屬於intersect前面的集合又屬於intersect後面的集合 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; SQL> --差集：只屬於minus前面的集合而不屬於minus後面的集合 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;2.在Sqlplus下實際執行的結果錄屏
SQL> /*
SQL> 查詢10和20號部門的員工
SQL> 1. select * from emp where deptno in (10,20);
SQL> 2. select * from emp where deptno=10 or deptno=20;
SQL> 3. 集合運算
SQL> select * from emp where deptno=10
SQL> +
SQL> select * from emp where deptno=20;
SQL> */
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> --利用集合運算實現group by的增強
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
SQL> 4. 可以使用括號
SQL> */
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> 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
DEPTNO JOB SUM(SAL)
------ --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已選擇13行。
SQL> break on null
SQL> select deptno,job,sum(sal) from emp 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> --打開sql執行時間
SQL> set timing on
SQL> select deptno,job,sum(sal) from emp 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.01
SQL> set timing off
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
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
SQL> spool off
