[原创]子查询汇总
参考:《SQL查询初学者指南》第二版,机械工业出版社。
子查询定义:子查询就是存在于一条select语句的一个子句中的另一条select语句,在join部分(《多表连 接(join)小结》)我们学习了from之后的子查询。
sql定义了三种类型的子查询。
1.行子查询
返回一行的嵌套select表达式。
大多数商业数据库并不支持行子查询,所以不说了。
2.表子查询
定义:返回一个表的嵌套select表达式。
大约有三种情况的应用。
第一种情况
在大多数sql的实现中,我们可以把from子句中的任何表名替换成一条完整的select语句.当然,必须分配一个相关名称。
例如select p.name, p.id from (select * from students) as p
但必须有as p ,如果是:
select p.name, p.id from (select * from students) 或者:
select p.name, p.id from select * from students
则错误了,因为没有分配名称
第二种情况
在可以指定一个表名的任何地方,都可以一个括号括起来的join子句替换。
在两个表的一个完整join语句出现的任何一个地方,我们都可以只用一个表名来代替它。
所谓“两个表的一个完整join语句”也即“join子句”,就是指如“A join B on A.a= B.b”这样一个完整的句子
关于第二种情况的详情,请参考《多表连 接(join)小结》
第三种情况
可以把一个表查询作为一个in、some、any、all、exists谓词的比较值列表的来源。
例如:Select * from students where students.area in (select area.Name from areas)
这里,子select语句应该能选出一个列,这个列的类型应该能够和students.area进行比较。
Some、any、all谓词用法举例:
在sql标准中,Some和any是等同的,意味着只要不等式(或等式)对some或any后边的列中的任何一项成立,则命题为真
all意味着不等式(或等式)对all后边的列中的所有项成立时,命题为真。
举例说明:
一个表t_group如下
在如下语句中
有两个select语句,分别是(1)和(2).
语句(1)先从表中选出所有的id,构成一列,成为列B,如图:
列B有两个值,分别为1、2
然后进入语句(2),语句(2)对表中所有项进行筛选,筛选到某一项时,只要该项的id能在列B中找到任何一个数字b,使得该项的id大于b,则该项就入选最终结果。
可以看到,1能够从B中找到1, 使得1>=B.1故id为1的项出现在最终结果中。id为2的项同理也出现在结果中,所以最终结果就是:
同样道理,select * from t_group where id > some(select id from t_group)的结果就是
因为2可以找到2>B.1,而1从B中找不到任何比1小的数,所以只有2进入了最终结果集。
all是一样的道理,不再赘述。
All some any 之前皆可用 > 、 = 、<、<=、>=、<>来进行筛选
exists用法请参考:http://www.cnblogs.com/ybwang/archive/2010/06/06/1752608.html
另外
insert into 语句中的values of 语句可以用一个select语句替换。
这使得insert into语句在复制数据方面显现出强大的能力。
例子一:
Insert into Products
(ProductName, RetailPrice, CategoryID)
select 'Hot Dog Spinner' as ProductName,
895 as RetailPrice, CategoryID
from Categories
where CategoryDescription = 'Bikes'
例子二:
insert into Engagements_Archive
(EngagementName, EngagementId, EngagementDescription, EndDate)
select Engagements.* from Engagements
where Engagements.EndDate < '2008-10-12'
3.标量子查询
定义:返回一列并且不多于一行的一个嵌套select表达式
要点:
在可以使用一个值表达式的任何地方都能够使用一个标量子查询。
标量子查询允许我们从另一个表中获取单一的值。
这个表不一定必须是主查询的from子句中的表。
标量子查询所获取的单个值,我们可以在select子句所请求的列的列表中使用。也可以作为where子句中的一个比较值。
标量子查询的例子:
例子1:在select子句所请求的列的列表中使用
有如下两个表,一个Orders一个Customers,一对多关系。
给出2007年10月3日配送的所有订单,以及每个订单相关的客户的姓氏
这个查询可以用join来实现,但这里我们用子查询实现。
Select Orders.OrderNumber, Orders.OrderDate, Orders.ShipDate,
(select Customers.CustLastName from Customers
where Customers.CustomerID = Orders.CustomerID)
from Orders where Orders.ShipDate = '2007-10-03'
这里我们使用一个子查询来产生一个输出列:
在子查询中,我们必须限制CustomerID的值,以保证结果不会是多行的。
列出所有客户的名字并统计他们下的订单数
Select Customers.FirstName, Customers.LastName,
(select count(*) from Orders
where Customers.CustomerID = Orders.CustomerID)
as CountOfOrders
from Customers
只要这个子查询返回的是个单值,便可以这样用。
列出客户名字以及他们最后一次下订单的日期。
Select Customers.FirstName, Customers.LastName,
(select max(OrderDate) from Orders
where Customers.CustomerID = Orders.CustomerID)
as LastDate
from Customers
例子2:作为where子句的比较值:
SELECT SUM(Sales) FROM Store_Information WHERE Store_name = (SELECT store_name FROM
Geography WHERE region_name = 'West' )
select 学号,年龄,性别,系名 from 学生 where 年龄 >(select max(年龄) from 学生 where 系名=计算机系)
你要保证子select语句的结果是一个单值,并且其数据类型是可参与比较的。
例子3:在可以使用一个值表达式的任何地方都能够使用一个标量子查询:
update Engagaments
set Engagements.ContractPrice =
Round(1.15 * (EndDate – StartDate + 1) *
(select EntPricePerDay
from Entertainers
where Entertainers.EntertainerID =
Engagemens.EntertainerID), 0)
总结
我们可以在子查询中使用父一级的from子句中的任何表的任何列。我想父一级的父一级的的表中的任何列应该也是可以使用的吧。
子查询的作用有二:产生输出列或者用作过滤器。
当查询结构复杂时,当该查询中不同地方都用到了同一个表的时候,不妨在不同的地方赋予该表不同的别名,以免产生混淆。
posted on 2010-06-05 22:16 ybwang1989 阅读(2728) 评论(1) 编辑 收藏 举报