SQL> --需求：查找部门号为10和部门号为20的员工
SQL> /*
SQL> 查询方法：
SQL> 1、select * from emp where deptno=10 or deptno=20
SQL> 2、select * from emp where deptno in (10,20)
SQL> 3、集合运算
SQL> 集合运算是操作两个或两个以上的集合
SQL> 所以集合操作的写法为：
SQL> select * from emp where deptno=10
SQL> union
SQL> select * from emp where deptno=20
SQL> */
SQL> --下面把上面三种方法都实现以下
SQL> select * from emp 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 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --方法一
SQL> --方法二：
SQL> select * from emp where deptno in (10,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 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -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 * 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 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --其中union又分为union和union all,union表示重复的只取一次，但是union all为有多少次重复就取多少次
SQL> --set timming on 可以知道某次查询所使用的时间
SQL> --那么利用集合查询跟group bY的增强 哪个更好呢？
SQL> --我们先来使用group by的增强：
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
30 9400
29025
已选择13行。
SQL> --上面的例子是查询各个部门的总薪水
SQL> --下面使用集合操作
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> 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
30 9400
29025
已选择13行。
SQL> --在对应的位置加入相应的null的数据转换到相应的类型，让集合之间的列的数量和数据类型一致
SQL> --如果想让上面的数据好看一些，可以使用如下语句
SQL> break on deptno skip 2
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
MANAGER 2450
PRESIDENT 5000
8750
20 ANALYST 6000
CLERK 1900
MANAGER 2975
10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
SQL> --我们可以比较一下这两种查询 哪一种的效率更高
SQL> --通过设置 set timing on
SQL> set timing on
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
MANAGER 2450
PRESIDENT 5000
8750
20 ANALYST 6000
CLERK 1900
MANAGER 2975
10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
已用时间: 00: 00: 00.04
SQL> --这是group by的增强
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
已用时间: 00: 00: 00.02
SQL> --如上，group By的增强的查询效率高于集合操作
SQL> host cls
SQL> --交集：只属于共有的部分
SQL> --例如查询两个薪水等级中在同样范围的员工
SQL> select * from emp where sal between 700 and 1300
2 intersect
3 select * from emp where sal between 1201 and 1400;
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
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已用时间: 00: 00: 00.09
SQL> --下面我们把间隔取消
SQL> break on null
SQL> --差集：即只属于第一个集合 不属于第二个集合
SQL> --我们只需要把上面的intersect改成minus即可
SQL> ed
已写入 file afiedt.buf
1 select * from emp where sal between 700 and 1300
2 minus
3* select * from emp where sal between 1201 and 1400
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
已用时间: 00: 00: 00.07
/*
小结：
如果想使用集合运算，要注意
1、各集合的列数和类型要一致
2、括号可以改变和集合运算的优先级
3、集合运算采用第一个语句的表头作为表头
SQL> spool off
Oracle集合操作
最新推荐文章于 2021-04-04 08:27:44 发布
173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
