Oracle子查询相关内容(包含TOP-N查询和分页查询)

本节介绍Oracle子查询的相关内容：

实例用到的数据为oracle中scott用户下的emp员工表，dept部门表，数据如下：

 

 

一、子查询

1、概念：嵌入在一个查询中的另一个查询语句，也就是说一个查询作为另一个查询的条件，这个查询称为子查询。

那么可以使用子查询的位置有select后面、from后面、where后面以及having后面。

2、分类：（1）单行子查询：查询结果只返回一行数据

　　　　  （2）多行子查询：查询结果返回多行数据，多行子查询的操作符有IN,ALL,ANY,具体用法实例中说明。

3、示例说明：

 Example1：查找每个部门的员工数量:

select deptno,dname,(select count(*) from emp e where e.deptno=d.deptno) amount from dept d;

此处子查询位于select后面，是每个部门的员工总人数。

Example2:查找工资大于部门平均工资的员工

select ename,sal,e.deptno from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) m 
where e.deptno=m.deptno and e.sal>m.avgsal;

此处子查询位于from后面，是每个部门的平均工资，将这个结果看做一张新表m，再加上查询条件即可。

Example3：查找和scott相同职位的员工信息

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

此处子查询位于where条件中，是和scott员工一样的职位。

Example4:查询部门平均工资大于30号部门最高工资的部门信息。

select deptno,avg(sal) from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);

此处子查询位于having子句中，是30号部门的最高工资。

Example5:查询部门是开发部或销售部的员工信息

select * from emp where deptno in(select deptno from dept where dname='RESEARCH' or dname='SALES');

此处用到了多行子查询的IN操作符用来获取RESEARCH和SALES部门的部门号，用来限制一个范围。

Example6:获取工资大于30号部门所有员工工资的信息。

select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);

此处用到了多行子查询中的ALL操作符，用于获取30号部门的所有工资信息，这里all起的主要作用是为了获得30号部门的最大工资，大于所有的意思就是大于最大的即可。

Example7:获取工资大于30号部门任意员工工资的信息。

select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);

此处用到了多行子查询中的any操作符，用于获取30号部门的工资信息，这里any的作用和all不同，主要取最小工资，任意就是说大于这些工资里面任意一个也就是大于最小的工资即可。

 二、oracle中TOP-N查询：

概念：用于获取一个查询中的前N条记录，需要借助rownum伪列来实现，rownum伪列，oracle为每个查询自动生成的伪列，物理上并不存在，查询中经常涉及多个表，但每个查询只有一列伪列。

Example:查找部门号为20和30的工资最高的5个员工信息

select * from (select * from emp where deptno in(20,30) order by sal desc) where rownum<=5;

这里在from后加了一个子查询，那么有个问题出现了，为什么这里不直接写而是要引入一个子查询呢，先来看看不加的结果：

select * from emp e where e.deptno in(20,30) and rownum<=5 order by e.sal desc;

很明显3000才是最高的工资，那是什么原因导致了这样的结果呢？是因为oracle中对select查询语句的执行顺序是先where条件后order by排序，也就是说先取了5行在对这5行进行排序，而正确的顺序应该是所有20,30部门的员工工资先进行排序在取5行

 三、Oracle分页查询的应用：

 概念：分页查询，顾名思义，控制查询结果的范围，得到我们想要的部分数据。

Example:获取员工表中20,30部门按工资降序以后的第4页也就是第7,8两条数据

select * from (select rownum rowline,emp1.* from (select * from emp where deptno in (20,30) 
order by sal) emp1 where rownum<=10) emp2 where emp2.rowline>=7 and emp2.rowline<=8;

这里或许稍微有点复杂，首先为什么不这样写

select * from (select * from emp where deptno in(20,30) order by sal desc) where rownum>=7 and rownum<=8;

这个查询永远也不会有数据生成，为什么呢，因为当内层查询产生第一条记录时，oracle为其伪列赋值rownum=1，

外层查询判rownum>=7 and rownum<=8不符合条件去除记录，当第二条记录产生时，oracle仍然会为其伪列赋值rownum=1，

外层判断仍然不会通过，这样无论内层查询产生多少数据都会因为外层查询的条件不符合记录而流失数据。

 

而想要避免这样的情况发生，就需要将伪列当成一个查询中的字段，将它不在看做“伪列”，而是真正的一个字段，

这样就需要在外面在嵌套一层查询将伪列做成一个物理上存在的字段，而最后我们只需要将外层查询的条件改为内层查询中“真实”存在的伪列即可。

 

 

 

 

子查询的相关内容总结完毕，有不明处请多多指教。                                                                                           2018-08-13    15:51:41