子查询汇总

原文地址：http://www.cnblogs.com/ybwang/archive/2010/06/05/1752429.html

参考:《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子句中的任何表的任何列。我想父一级的父一级的的表中的任何列应该也是可以使用的吧。

子查询的作用有二：产生输出列或者用作过滤器。

当查询结构复杂时，当该查询中不同地方都用到了同一个表的时候，不妨在不同的地方赋予该表不同的别名，以免产生混淆。