高级子查询
1.多列子查询
注意的问题
5.一般不在子查询中排序，没有意义（但是在TOP-N分析中需要对子查询排序）
6.一般先执行自查询，后执行主查询 ，但是相关子查询例外
7.单行子查询只能使用单行操作符，多行子查询只能使用多行子查询
8.子查询中的null值
.
in 在集合中 SQL> --查询部门名称是SALES和ACCOUNTING的员工 SQL> select * 2 from emp 3 where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING'); 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 行。 SQL> --any: 和集合中的任意一个值比较 SQL> --查询工资比30号部门任意一个员工高的员工信息 SQL> select * 2 from emp 3 where sal > any (select sal from emp 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 已选择 12 行。 SQL> ed 已写入 file afiedt.buf 1 select * 2 from emp 3* where sal > (select min(sal) from emp where deptno=30) SQL> / 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择 12 行。 SQL> --all 和集合中的所有值比较 SQL> --查询工资比30号部门所有员工高的员工信息 SQL> select * 2 from emp 3 where sal > all (select sal from emp 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> ed 已写入 file afiedt.buf 1 select * 2 from emp 3* where sal > (select max(sal) from emp where deptno=30) SQL> / 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> host cls SQL> --多行子查询中的null SQL> --not in (10,20,null) SQL> --查询不是老板的员工 SQL> select * from emp; 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 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 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 已选择 14 行。 SQL> select * 2 from emp 3 where empno not in (select mgr from emp); 未选定行 SQL> --查询是老板的员工 SQL> select * 2 from emp 3 where empno in (select mgr from emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-12月-81 3000 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7839 KING PRESIDENT 17-11月-81 5000 10 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 已选择 6 行。 SQL> select * 2 from emp 3 where empno not in (select mgr from emp where mgr is not null); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10 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 已选择 8 行。
9.相关子查询
将主查询中的值，作为值传递给子查询
/*
SQL> 临时表:1. create global temporary table *****
SQL> 2. 自动创建: order by
SQL> 特点：当事务或者会话结束的时候，表中的数据自动删除
SQL> */
SQL> create global temporary table test2
2 (tid number,tname varchar2(20))
3 on commit delete rows;
表已创建。
SQL> insert into test2 values(1,'Tom');
已创建 1 行。
SQL> select * from test2;
TID TNAME
---------- --------------------
1 Tom
SQL> commit;
提交完成。
SQL> select * from test2;
未选定行
SQL> desc test2
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
TID NUMBER
TNAME VARCHAR2(20)
SQL> host cls
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> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
2 from emp e
3 where sal > (select avg(sal) from emp where deptno=e.deptno);
EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7499 ALLEN 1600 1566.66667
7566 JONES 2975 2175
7698 BLAKE 2850 1566.66667
7788 SCOTT 3000 2175
7839 KING 5000 2916.66667
7902 FORD 3000 2175
We only live once, and time just goes by.
浙公网安备 33010602011771号