文章目录
子查询
子查询：sub query, 查询是在某个查询结果之上进行的。(一条select语句内部包含了另外一条select语句)
子查询类型
1.单行子查询
举一个例子，查询职位为MANAGER,薪水大于2450的员工。
通过下面 的例子可以知道两点：
- 单行子查询只能使用单行操作符。
- 主查询和子查询是1：N的关系。
SQL> select ename,job,sal
2 from emp
3 where job = (select job
4 from emp
5 where empno = 7566)
6 and sal > (select sal
7 from emp
8 where empno = 7782);
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
SQL> --查询工资比SCOTT高的员工信息
SQL> --1. SCOTT的工资
SQL> select sal from emp where ename='SCOTT';
SAL
----------
3000
SQL> --2. 查询比3000高的员工
SQL> select * from emp where sal >3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> --子查询所要解决的问题：不能一步求解
SQL> select * from emp
2 where sal > (select sal
3 from emp
4 where ename='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
2.多行子查询
- 多行子查询只能使用多行操作符。
- 主查询和子查询是1：N的关系。
1.in：等于列表中的任何一个
SQL> --查询部门为SALES和ACCOUNTING的员工(方式一)
SQL> select empno,ename,sal
2 from emp
3 where deptno in (select deptno
4 from dept
5 where dname = 'ACCOUNTING' or dname = 'SALES');
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1500
7900 JAMES 950
7934 MILLER 1300
已选择 9 行。
SQL> --查询部门为SALES和ACCOUNTING的员工(方式二)
SQL> --sql不符合题目逻辑
SQL> select e.*
2 from emp e,dept d
3 where e.deptno = d.deptno
4 and d.dname = 'ACCOUNTING' or dname = 'SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
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
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择 17 行。
SQL> --sql的d.dname = 'ACCOUNTING' or dname = 'SALES'要加上括号。
SQL> select e.*
2 from emp e,dept d
3 where e.deptno = d.deptno
4 and (d.dname = 'ACCOUNTING' or dname = 'SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择 9 行。
2.any：和子查询返回的任意一个值做比较
SQL> --any: 和集合中的任意一个值比较。
SQL> --查询工资比30号部门任意一个员工高的员工信息。
SQL> select *
2 from emp
3 where sal > any(select sal
4 from emp
5 where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
已选择 12 行。
SQL> --any是和子查询返回的任意一个值做比较，根据题目要求只要比最小的大就可以满足条件了，下面的查询结果集和上面一样。
SQL> select *
2 from emp
3 where sal > (select min(sal)
4 from emp
5 where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
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
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
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
已选择 12 行。
3.all：和子查询返回的所有值作比较
SQL> --all:和集合中的所有值比较
SQL> --查询工资比30号部门所有员工高的员工信息
SQL> select *
2 from emp
3 where sal > all (select sal
4 from emp
5 where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
SQL> --all是和子查询返回的所有值作比较，根据题目要求只要比最大的大就可以满足条件了，下面的查询结果集和上面一样。
SQL> select *
2 from emp
3 where sal > (select max(sal)
4 from emp
5 where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
子查询要注意的问题
1、括号
2、合理的书写风格
3、将子查询放在比较条件的右侧
4、可以在主查询的 where select having from 后面使用子查询
5、不可以在 group by 使用子查询
6、强调 from 后面的子查询
7、主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
8、一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序
9、一般先执行子查询，再执行主查询；但相关子查询例外
10、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
11、子查询中的null
4、可以在主查询的 select from where having 后面使用子查询
1.select后面使用子查询
SQL> --3、可以在主查询的where select having from 后面使用子查询
SQL> select empno,ename,sal,(select job from emp where empno=7839) 第四列
2 from emp;
EMPNO ENAME SAL 第四列
---------- ---------- ---------- ---------
7369 SMITH 800 PRESIDENT
7499 ALLEN 1600 PRESIDENT
7521 WARD 1250 PRESIDENT
7566 JONES 2975 PRESIDENT
7654 MARTIN 1250 PRESIDENT
7698 BLAKE 2850 PRESIDENT
7782 CLARK 2450 PRESIDENT
7788 SCOTT 3000 PRESIDENT
7839 KING 5000 PRESIDENT
7844 TURNER 1500 PRESIDENT
7876 ADAMS 1100 PRESIDENT
7900 JAMES 950 PRESIDENT
7902 FORD 3000 PRESIDENT
7934 MILLER 1300 PRESIDENT
已选择 14 行。
2.where后面使用子查询
SQL> select *
2 from emp
3 where sal >(select sal
4 from emp
5 where ename='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
3.having后面使用子查询
SQL> --3、可以在主查询的where select having from 后面使用子查询
SQL> --查询各部门中最低工资大于20号部门最低工资。
SQL> select deptno,min(sal)
2 from emp
3 group by deptno
4 having min(sal) > (select min(sal)
5 from emp
6 where deptno = 20);
DEPTNO MIN(SAL)
---------- ----------
30 950
10 1300
5、不可以在 group by 使用子查询
查看group by字句,你怎么在后面加上子查询，也没办法加子查询。
SQL> select deptno
2 from emp
3 group by deptno;
DEPTNO
----------
30
20
10
6、强调 from 后面的子查询
SQL> --强调 from 后面的子查询
SQL> --查询员工信息：员工号 名称 薪水
SQL> /*
SQL> select *
SQL> ______________这个这个横线上怎么写,可以满足题目要求。
SQL> */
SQL> select *
2 from (select empno,ename,sal
3 from emp);
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
已选择 14 行。
SQL> --查询员工信息：员工号 名称 薪水
SQL> /*
SQL> select *
SQL> ______________这个这个横线上怎么写,可以满足题目要求。
SQL> */
SQL> select *
2 from (select empno,ename,sal,sal*12
3 from emp);
EMPNO ENAME SAL SAL*12
---------- ---------- ---------- ----------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
已选择 14 行。
7、主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
- 如果子查询和多表查询都能完成的查询,尽量使用多表查询（因为子查询会访问操作数据库多次）。
- 在Oracle数据库中，很多子查询最终会转为多表查询，那是怎么知道呢?通过–>SQL执行计划
SQL> --主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
SQL> --查询部门名称是SALES的员工（方式一）
SQL> select *
2 from emp
3 where deptno=(select deptno from dept where dname='SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择 6 行。
SQL> --查询部门名称是SALES的员工（方式二）
SQL> select e.*
2 from emp e,dept d
3 where e.deptno=d.deptno and d.dname='SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
8、一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序(rownum细节)
1.rownum细节
- rownum永远按照默认的顺序生成
- rownum只能使用< <=；不能使用> >=(因为Oracle是一个行式数据库,有了第一行才能有行。不能从中间行号开始，就类似造房子。)
- rownum 永远从1开始(因为Oracle是一个行式数据库)
2. rownum为什么排序后行号还是不变(临时表)
3.Oracle的分页图解
4.找到员工表工资最高的前三名
SQL> --rownum 行号
SQL> select rownum,empno,ename,sal from emp;
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
1 7369 SMITH 800
2 7499 ALLEN 1600
3 7521 WARD 1250
4 7566 JONES 2975
5 7654 MARTIN 1250
6 7698 BLAKE 2850
7 7782 CLARK 2450
8 7788 SCOTT 3000
9 7839 KING 5000
10 7844 TURNER 1500
11 7876 ADAMS 1100
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
12 7900 JAMES 950
13 7902 FORD 3000
14 7934 MILLER 1300
已选择 14 行。
SQL> select rownum,empno,ename,sal
2 from emp
3 where rownum<=3
4 order by sal desc;
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
2 7499 ALLEN 1600
3 7521 WARD 1250
1 7369 SMITH 800
SQL> /*
SQL> 关于rownum
SQL> 1、rownum永远按照默认的顺序生成
SQL> 2、rownum只能使用< <=；不能使用> >=
SQL> */
SQL> select rownum,empno,ename,sal from emp order by sal desc;
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
9 7839 KING 5000
13 7902 FORD 3000
8 7788 SCOTT 3000
4 7566 JONES 2975
6 7698 BLAKE 2850
7 7782 CLARK 2450
2 7499 ALLEN 1600
10 7844 TURNER 1500
14 7934 MILLER 1300
3 7521 WARD 1250
5 7654 MARTIN 1250
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
11 7876 ADAMS 1100
12 7900 JAMES 950
1 7369 SMITH 800
已选择 14 行。
SQL> --第一题
SQL> select rownum,empno,ename,sal
2 from (select * from emp order by sal desc)
3 where rownum<=3;
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
1 7839 KING 5000
2 7788 SCOTT 3000
3 7902 FORD 3000
SQL> --2、rownum只能使用< <=；不能使用> >=
SQL> --分页
SQL> select rownum,empno,ename,sal from emp
2 where rownum>=5 and rownum<=8;
未选定行
SQL> --rownum 永远从1开始
SQL> select rownum,empno,ename,sal from emp
2 where rownum>=5;
未选定行
SQL> ed
已写入 file afiedt.buf
1 select rownum,empno,ename,sal from emp
2* where rownum<=8
SQL> /
ROWNUM EMPNO ENAME SAL
---------- ---------- ---------- ----------
1 7369 SMITH 800
2 7499 ALLEN 1600
3 7521 WARD 1250
4 7566 JONES 2975
5 7654 MARTIN 1250
6 7698 BLAKE 2850
7 7782 CLARK 2450
8 7788 SCOTT 3000
已选择 8 行。
SQL> --Oracle分页,取5到8行的记录
SQL> select *
2 from (select rownum r,e1.*
3 from (select * from emp order by sal) e1
4 where rownum <=8
5 )
6 where r >=5;
R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
6 7934 MILLER CLERK 7782 23-1月 -82 1300 10
7 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
8 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
SQL> /*
SQL> 标准表、索引表
SQL> 临时表:1
SQL> 创建方式
SQL> 1. create global temporary table ****
SQL> 2. 自动：排序
SQL> 特点：当事务或者会话结束的时候，表中的数据自动删除*/
SQL> */
SQL> --创建一张临时表
SQL> create global temporary table temptest1
2 (tid number ,tname varchar2(20));
表已创建。
SQL> --向表中插入第一条数据
SQL> insert into temptest1 values(1,'Tom');
已创建 1 行。
SQL> --向表中插入第二条数据
SQL> insert into temptest1 values(2,'Mary');
已创建 1 行。
SQL> --查询临时表数据
SQL> select * from temptest1;
TID TNAME
---------- --------------------
1 Tom
2 Mary
SQL> --提交事务后,临时表数据会自动删除。
SQL> commit;
提交完成。
SQL> --再次查询临时表数据，数据已经删除了。
SQL> select * from temptest1;
未选定行
9、一般先执行子查询，再执行主查询；但相关子查询例外
1.相关子查询图解
2.找到员工表中薪水大于本部门平均薪水的员工
SQL> --使用子查询实现
SQL> select e.empno,e.ename,e.sal,d.avgsal
2 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
3 where e.deptno=d.deptno and e.sal>d.avgsal;
EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7698 BLAKE 2850 1566.66667
7499 ALLEN 1600 1566.66667
7902 FORD 3000 2175
7788 SCOTT 3000 2175
7566 JONES 2975 2175
7839 KING 5000 2916.66667
已选择 6 行。
SQL> --使用相关子查询实现
SQL> --相关子查询： 将主查询中的值 作为参数传递给子查询
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
2 from emp e
3 where sal>(select avg(sal)
4 from emp
5 where deptno = e.deptno);
EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7499 ALLEN 1600 1566.66667
7698 BLAKE 2850 1566.66667
7566 JONES 2975 2175
7788 SCOTT 3000 2175
7902 FORD 3000 2175
7839 KING 5000 2916.66667
10、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
11、子查询中的null
1.单行子查询中的null
2.多行子查询中的null
SQL> --多行子查询中的null
SQL> --查询不是老板的员工信息
SQL> select *
2 from emp
3 where empno not in (select mgr
4 from emp);
未选定行
SQL> --其实在前面已经说过了，not in 关于null值的问题，在not in 中不能有null值，如有有将永远不会成立，也就查询任何不出数据。所以要过滤集合中的null值。
SQL> --SQL> select *
2 from emp
3 where empno not in (select mgr
4 from emp
5 where mgr is not null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择 8 行。
集合运算
1.并集
SQL> /*
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
SQL> 加上
SQL> select * from emp where deptno=20
SQL> */
SQL> select *
2 from emp
3 where empno not in (select mgr
4 from emp
5 where mgr is not null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择 8 行。
SQL> --前面已经说过 group by增强,3个sql==gorup 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> -使用union连接3个sql==gorup by增强出现sql错误,那就要说说集合运算要注意细节了在第4点。
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: 查询块具有不正确的结果列数
2.交集
3.差集
4.集合运算要注意事项
注意的问题：
1、参与运算的各个集合必须列数相同，且类型一致，在Mysql中集合列数相同，跟数据类型无关。
2、采用第一个集合作为最后的表头
3、order by永远在最后查询语句后
4、可以使用括号改变集合执行的顺序
1.参与运算的各个集合必须列数相同 且类型一致
- 尽量不要使用集合(union)运算查询。(union和group by增强都能完成报表查询,group by增强效率更高。)
--下面3个sql也可以完成group by增强报表统计，但是需要各个集合必须列数相同 且类型一致。
SQL> --同一个部门的不同职位的薪水总和
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4 union
5 --各部门薪水总和
6 select deptno,to_char(null),sum(sal)
7 from emp
8 group by deptno
9 union
10 --员工薪水总和
11 select to_number(null),to_char(null),sum(sal)
12 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> --break on deptno skip 2
SQL> --现在有个问题就是， union 和 gorup by 都能完成报表统计那使用谁呢?
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
30 9400
29025
已选择 13 行。
已用时间: 00: 00: 00.02
SQL> --同一个部门的不同职位的薪水总和
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4 union
5 --各部门薪水总和
6 select deptno,to_char(null),sum(sal)
7 from emp
8 group by deptno
9 union
10 --员工薪水总和
11 select to_number(null),to_char(null),sum(sal)
12 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 行。
已用时间: 00: 00: 00.07
SQL> --通过执行时间使用group by效率更高
SQL> --关闭显示SQL 执行的时间
SQL> set timing off
2.order by永远在最后查询语句后
- 集合运算在oracle中order by永远在最后查询语句后。
- 集合运算在Mysql中order by不能直接使用，需要对查询语句使用括号才行，若要orderby生效必须搭配limit，limit使用限定的最大数即可。
Mysql集合运算示例
Oracle示例
SQL> --集合运算order by不出现在查询最后,会出现语法报错。
SQL> --同一个部门的不同职位的薪水总和
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job order by deptno desc
4 union
5 --各部门薪水总和
6 select deptno,to_char(null),sum(sal)
7 from emp
8 group by deptno
9 union
10 --员工薪水总和
11 select to_number(null),to_char(null),sum(sal)
12 from emp;
union
*
第 4 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> --集合运算order by永远在最后查询语句后。
SQL> --同一个部门的不同职位的薪水总和
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4 union
5 --各部门薪水总和
6 select deptno,to_char(null),sum(sal)
7 from emp
8 group by deptno
9 union
10 --员工薪水总和
11 select to_number(null),to_char(null),sum(sal)
12 from emp order by deptno desc;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
29025
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
已选择 13 行。
553
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
