Oracle 多行、多列子查询

本文使用到的是oracle数据库scott方案所带的表,scott是oracle数据库自带的方案,使用前请确保其解锁

一、多行子查询

多行子查询子查询是嵌入在其他Sql语句中的select语句,Oracle子查询分为两种:一种是单行子查询,一种是多行子查询

1、单行子查询

单行子查询的select语句只返回一行数据,也就是说嵌入在其他Sql语句中的那个select查询值返回一行数据。

例:查询emp表中,与Smith同一部门的所有员工

select * from emp where deptno=
(select deptno from emp where ENAME='SMITH');--这里的select查询只返回一行数据

所以,我们把子查询的嵌入的select语句至返回一行数据的这类子查询,称为单行子查询

2、多行子查询

了解了单行子查询的原理,那么多行子查询自然而然的就知道了,多行子查询就是嵌入在其他Sql语句中的select查询返回多行数据

例:查询所有员工中工作和部门10的工作一样的员工信息

select * from emp where job in
(select  distinct job from emp where deptno=10) --这里的select查询返回多行记录

注意左图红框中的关键字只能用in,不能使用'=','='号表示一对一,in表示处于一个范围(用来处理子查询返回多行记录),如果这里使用'='号,也就是执行如下语句:

select * from emp where job =
(select distinct job from emp where deptno=10)  --这里的select查询返回多行记录

3、多行子查询中的特殊操作符

虽然in能解决多行子查询中的=的问题,但是如果要和子查询的结果集中的字段比较大小呢?显然用in是不够用的!!!ok,下面即将使用的操作符,就是来解决这个问题的.

3.1、多行子查询中的all操作符

例:查询比部门30所有员工的工资都要高度员工信息

select * from emp where sal>
(select sal from emp where deptno=30)   --这里的select查询返回多行记录

执行sql之后发现和=是一样的问题,因为'>'表示一对一的关系,而子查询返回多个结果集,所以报错了,那怎么办呢?总不可能用in吧!这里就要使用all了,如下代码:

select * from emp where sal>
all (select sal from emp where deptno=30)   --这里的select查询返回多行记录

在子查询前面加上all关键字,表示当检索emp表时,只检索出哪些比(子查询结果集中最大的还要大)的数据行   

所以上面的代码也可以这样表示:

select * from emp where sal>
(select max(sal) from emp where deptno=30)

3.2、多行子查询中的any操作符

在了解了all操作符之后,any操作符也就好理解了。

select * from emp where sal>
any (select sal from emp where deptno=30)   --这里的select查询返回多行记录

这段代码表示:检索出emp表,只检索那些(只要比子查询的结果集的任意一行大)就行的数据行,所以上面的代码也相当于:

select * from emp where sal>
(select min(sal) from emp where deptno=30)   --这里的select查询返回多行记录

二、多列子查询

例1:查询与smith部门和岗位完全相同的员工

1、首先使用多行子查询来解决这个问题

select * from emp where deptno=
(select deptno from emp where ename='SMITH') 
and job=
(select job from emp where ename='SMITH') 

2、使用多列子查询来解决这个问题

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH')  --列的顺序一定要相同

加入换了列的顺序,代码如下:

select * from emp where (job,deptno)=(select deptno,job from emp where ename='SMITH')  --列的顺序一定要相同

例2:查询高于自己部门平均工资的员工信息

select * from emp a,(select deptno,ceil(avg(sal)) as avgsal from emp group by deptno) b
where a.deptno=b.deptno and a.sal>b.avgsal

解决思路:通过分组函数求出各个部门的平均薪水,然后将平均薪水通过deptno和emp进行连表查询,然后比较薪水和平均值,得出结果集。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库

mysql数据操作语句

?imageMogr2/blur/1x0/quality/75|watermark/1/image/aHR0cDovL29zNzhmNGhueS5ia3QuY2...

1965
来自专栏java达人

mysql left( right ) join使用on 与where 筛选的差异

有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。 可能只看着两个关键字看不出任...

2347
来自专栏Albert陈凯

2018-07-20 oracle优化:避免全表扫描

例如:在City-State-Zip列创建了三列复合索引,那么仅对State列限定条件不能使用这个索引,因为State不是索引的主列。

2344
来自专栏自由而无用的灵魂的碎碎念

分享:Oracle sql语句优化

最近做查询时,写的一条查询语句用了两个IN,导致tuexdo服务积压了不少,用户没骂就不错了。最后经过技术经理的点拨,sql语句性能提升了大约10倍,主要用了表...

1561
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战五——SQL查询

Oracle SQL SQL学习是重点,请仔细阅读。 ? O Oracle 结构化查询语言(Structured Query Language)简称SQL(...

3444
来自专栏小灰灰

mysql之基本语法

本篇将主要集中在mysql的使用上,包括如何创建标,如何进行insert,update,select,delete,以及一些常见的sql中关键字的使用姿势

62722
来自专栏张狗蛋的技术之路

Mysql探索(一):B-Tree索引

MySQL是目前业界最为流行的关系型数据库之一,而索引的优化也是数据库性能优化的关键之一。所以,充分地了解MySQL索引有助于提升开发人员对MySQL数...

1943
来自专栏技术博客

Oracle初级性能优化总结

  关于对Oracle数据库查询性能优化的一个简要的总结。 从来数据库优化都是一项艰巨的任务。对于大数据量,访问频繁的系统,优化工作显得尤为重要。由于Oracl...

1333
来自专栏文渊之博

探索SQL Server元数据(三):索引元数据

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,...

2091
来自专栏Linux运维学习之路

MySQL索引

索引管理 索引是什么? 索引就好比一本书的目录,它会让你更快的找到内容; 让获取的数据更有目的性,从而提高数据库检索数据的性能; 索引建立在表的列上(字段)。 ...

4335

扫码关注云+社区

领取腾讯云代金券