子查询实例

select

(select  count(*) total from emp) total,

(select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980",

(select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981",

(select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982",

(select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987"

from dual

select  count(e.empno) total,

sum(decode(to_char(e.hiredate,'YYYY'),'1980',1,0)) "1980"，

sum(decode(to_char(e.hiredate,'YYYY'),'1981',1,0)) "1981"，

sum(decode(to_char(e.hiredate,'YYYY'),'1982',1,0)) "1982"，

sum(decode(to_char(e.hiredate,'YYYY'),'1987',1,0)) "1987"

from emp  e

1oracle分页是通过嵌套子查询完成的

select rownum,r,empno,ENAME,sal from 

(select rownum r , empno,ENAME,sal

from (select rownum,empno,ENAME,sal from emp   order by sal desc ) a1  where  rownum<=8) a2

where r>=5

--执行计划：explain plan for select .......

--查看执行计划：select * from table(dbms_xplan.display);

explain plan for

(

select * 

from emp e,(select e.deptno,avg(e.sal) avgsal from emp e group by e.deptno) d

where e.deptno=d.deptno and e.sal>d.avgsal

)

select * from table(dbms_xplan.display);

select * 

from emp e

where e.empno not in

(select mgr from emp where  mgr is not null) 

子查询的类型 （1）单行子查询:只返回一条记录    操作符：=,>,>=,<,<=,<>不等于 例子：查询员工信息，要求：职位与7566员工一样，薪水大于7782的  select * from emp where job= (select job from emp where empno=7566) and  sal>(select sal from emp where empno=7782);     （2）多行子查询：返回多条记录     操作符：in 等于列表中的任何一个            any 和子查询返回的任意一个值作比较            all 和子查询返回的所有值比较 例子：(1)in  查询部门名称是sales 和accounting 的员工信息   select *from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');   select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');    (2)any  查询工资比30号部门任意一个员工高的员工信息      select * from emp where sal> any ( select sal from emp where deptno=30);      select * from emp where sal>(select min(sal) from emp where deptno=30);    (3)all  查询工资比30号部门所有一个员工高的员工信息      select * from emp where sal> all( select sal from emp where deptno=30);      select * from emp where sal>(select max(sal) from emp where deptno=30);

--赵春各部门薪资大于平均薪资的员工  (相关子查询)

select e.*, (select avg(sal) from emp where deptno=e.deptno) avgSal

from emp e

where e.sal>(select avg(sal) from emp where deptno=e.deptno)

--TOP-N问题分析

--rownum是oracle伪列  是在生成的时候就默认存在的行号  不会因为我们的排序发生改变  

select rownum,e.sal 

from emp e

where rownum <=3

order by  e.sal desc

--利用子查询的时候 新生成的行号rownum找到薪资前三名的员工信息

select rownum, info.*

from (

select * from emp e

order by e.sal desc

 ) info

 where rownum<=3

--查询销售部的员工信息

--where 子查询 

select * 

from emp 

where deptno=(select deptno from dept t where t.dname='SALES')

--多表查询（理论上更优  只有一个from 一次操作数据库）

select * from emp e,dept d

where e.deptno=d.deptno and d.dname='SALES'

from 后面可以是表，也可以是查询语句

--where 子查询

select *

from emp 

where sal > (select sal

             from emp

             where ename = 'SCOTT' );

--select 子查询           

 select ename,(select job from emp where empno=7839) 

 from emp

 --from 子查询

 select ename 

 from (select * from emp)

 --having 子查询

 select deptno,avg(sal)

 from emp 

 group by deptno

 having avg(sal)>(select avg(sal) from emp where deptno=20)

1可以使用子查询的位置：where，select,having,from

2不可以使用子查询的位置：group by

3主查询和子查询可以不是同一张表 

4一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序 

5一般先执行子查询，再执行主查询；但相关子查询例外 

6单行子查询只能使用单行操作符；多行子查询只能使用多行操作符 

7注意:子查询中是null值问题

例：查询工资比Scott高的员工 

select * from emp where sal > (select sal             from emp             where ename = 'SCOTT' );

1. 自连接缺陷：多表查询》》笛卡尔集》》》会带来至少平方级别的数据累计（不适合大表）

2. 引出层次查询（实际是是单表查询  没有笛卡尔集）

3. 需要遍历树结构 

4. 节点连接条件（上一节点的员工号是下一节点的老板）connect by prior empno=mgr

5. 需要设置开始节点 start with

6. 各有优缺点：自连接直观（但不适合大表） 层次查询性能更好（但是不直观）