关于Oracle子查询各大用法详解

Z时代
2024-01-10
分类：综合

database

子查询

一.概述:

子查询：一个select语句，作为另一条select语句语法的一部分。

select语句语法：

select distinct * | 字段 from 表名
where 查询条件
group by 分组字段 having 分组条件
order by 排序字段 asc | desc

二. 单行子查询

只返回一条记录

单行操作符

编写步骤，将一个需求拆分成多个子需求，依次完成每一个子需求，最后将组合子需求


--（一）单行子查询
--只返回一条记录
--需求：查询2012年1月用水量大于平均值的台账记录
--1用水量平均值
select avg(ac.usenum) from t_account ac where ac.year="2012"
 and  ac.month="01";
--2.查询2012年1月所有用水量
select * from t_account ac 
where ac.year="2012" and 
ac.month="01";
--合并
select * from t_account ac 
where ac.year="2012" and 
ac.month="01" and ac.usenum>=(
select avg(ac.usenum) from t_account ac where ac.year="2012" and 
ac.month="01"
);

三. 多行子查询

返回了多条记录

多行操作符


--（二）多行子查询
--返回了多条记录
--1查询地址编号为1 、3、4 的业主记录
select * from t_owners ow where ow.addressid in(1,3,4);
--2查询地址含有“花园”的业主的信息
--1.含有"花园"地址id
select ad.id from t_address ad where ad.name like "%花园%";
--2.整合
select * from t_owners ow where ow.addressid 
in(select ad.id from t_address ad where ad.name like "%花园%");
--方式2
select * from t_owners ow ,t_address ad 
where ow.addressid=ad.id and ad.name like "%花园%";
--3查询地址不含有“花园”的业主的信息
select * from t_owners ow where ow.addressid 
not in(select ad.id from t_address ad where ad.name like "%花园%");
--4查询2012年台账中，使用量大于2012年3月最大使用量的台账数据
--方式1;求最大值
--1.2012年3月最大使用量
select max(ac.usenum) from t_account ac where ac.year="2012" 
and ac.month="03";
--2.查询2012年台账中使用量大于13808
select * from t_account ac where ac.year="2012" 
 and ac.usenum>13808;
--整合
select * from t_account ac where ac.year="2012" 
 and ac.usenum>(select max(ac.usenum) from t_account ac where ac.year="2012" 
and ac.month="03");
--方式2;使用all运算符
--1.求2012年3月所有使用量
select ac.usenum from t_account ac where ac.year="2012" 
and ac.month="03";
--2.查询2012年台账大于3月份所有使用量
select * from t_account ac where ac.year="2012"
and ac.usenum >all(13808,13390);
--3整合
select * from t_account ac where ac.year="2012"
and  ac.usenum >all(select ac.usenum from t_account ac where ac.year="2012" 
and ac.month="03");

四. 嵌套子查询

嵌套子查询：在子查询中再次嵌入子查询


--（三）嵌套子查询
----嵌套子查询：在子查询中再次嵌入子查询
--查询在海淀区的小区名字中含有花园的业主记录
--1.查询区域id,名称为"海淀"
select a.id from t_area a where a.name="海淀";
--2 查询地址,条件,名称含"花园"和区域id
select ad.id from t_address ad 
where ad.name  like "%花园%" and
ad.areaid=(select a.id from t_area a where a.name="海淀");
--3 查询业主,条件;一组地址id
select * from t_owners ow where 
ow.addressid=(select ad.id from t_address ad 
where ad.name  like "%花园%" and
ad.areaid=(1));
--4.组合
select * from t_owners ow where 
ow.addressid=(select ad.id from t_address ad 
where ad.name  like "%花园%" and
ad.areaid=(select a.id from t_area a where a.name="海淀"));

五.标量子查询

标量子查询：子查询的语句执行的结果直接作为主查询的结果显示


--查询台账表中的用户年用水量的总和    以及  年平均用水量
--1.查询用水量总和
select sum(ac.usenum) from t_account ac;
--2.查询用水量平均值
select avg(ac.usenum) from t_account ac;
--3.将两个不相关的数据,使用虚表dual组合在一起
select
 (select sum(ac.usenum)from t_account ac) as 年用水量的总和,
 (select avg(ac.usenum) from t_account ac)as 年平均用水量
  from dual;

六.相关子查询

相关子查询：子查询依赖外面的主查询的结果


--查询显示业主编号，业主名称、地址和业主类型
select ow.id,ow.name,ad.name,ot.name
 from t_owners ow ,t_address ad,t_ownertype ot
where ow.addressid=ad.id 
and ow.ownertypeid=ot.id;
--方式2;相关查询
select ow.id as 业主编号,ow.name as 业主名称,
(select ad.name from t_address ad where ad.id=ow.addressid) as 地址,
(select ot.name from t_ownertype ot where ot.id=ow.ownertypeid) as 业主类型 
 from t_owners ow;
--查询显示业主编号、业主名称、地址、所属区域、业主分类
--方式一
select ow.id,ow.name,ad.name,ar.name,ot.name from 
 t_owners ow ,t_address ad, t_area ar,t_ownertype ot
 where ow.addressid=ad.id and ad.areaid=ar.id 
and ow.ownertypeid=ot.id;
--方式二;相关查询
select ow.id as 业主编号,ow.name as 业主名称,
(select ad.name from t_address ad where ad.id=ow.addressid) as 地址,
(select ar.name from t_area ar, t_address ad where ar.id=ad.id and ad.id=ow.addressid)as 所属区域,
(select ot.name from t_ownertype ot where ot.id=ow.ownertypeid) as 业主类型 
 from t_owners ow ;