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集合运算采用第一个语句的表头作为表头
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。