[原创]子查询汇总

参考:《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如下

clip_image001

在如下语句中

clip_image002

有两个select语句,分别是(1)和(2).

语句(1)先从表中选出所有的id,构成一列,成为列B,如图:

clip_image003

列B有两个值,分别为1、2

然后进入语句(2),语句(2)对表中所有项进行筛选,筛选到某一项时,只要该项的id能在列B中找到任何一个数字b,使得该项的id大于b,则该项就入选最终结果。

可以看到,1能够从B中找到1, 使得1>=B.1故id为1的项出现在最终结果中。id为2的项同理也出现在结果中,所以最终结果就是:

clip_image004

同样道理,select * from t_group where id > some(select id from t_group)的结果就是

clip_image005

因为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,一对多关系。

Untitled

 

给出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编辑  收藏  举报

导航