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

栏目: 数据库 · Oracle · 发布时间: 6年前

内容简介:select deptno,dname,(select count(*) from emp e where e.deptno=d.deptno) amount from dept d;

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

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

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

一、子查询

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;

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

此处子查询位于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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

此处用到了多行子查询中的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;

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

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

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

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

很明显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;

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

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

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,

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

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

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

子查询的相关内容总结完毕,有不明处请多多指教。

更多Oracle相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

Linux公社的RSS地址: https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-08/153768.htm


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

大数据大创新:阿里巴巴云上数据中台之道

大数据大创新:阿里巴巴云上数据中台之道

邓中华 / 电子工业出版社 / 2018-11 / 99

阿里巴巴云上数据中台正服务着阿里生态中的数十个业务板块、百余家公司、千万级客户,在帮助决策层看清甚至决定业态走向的同时,在上万个业务场景中应用并催生创新。 《大数据大创新:阿里巴巴云上数据中台之道》基于作者在阿里巴巴的十年大数据从业经历,精彩演绎云上数据中台之道。《大数据大创新:阿里巴巴云上数据中台之道》基于大数据探索的大趋势,讲述阿里巴巴云上数据中台顶层设计,再以实际案例详述阿里巴巴云上数......一起来看看 《大数据大创新:阿里巴巴云上数据中台之道》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具