数据库 \ SQL Server \ SQL 子查询

SQL 子查询

总点击14
简介:  查询(query):任何SQL语句都是查询。但此术语一般指SELECT语句。   SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。为什么要这样做呢?理解这个概念的最好方法是考察几个例子。利用子查询进行

  查询(query):任何SQL语句都是查询。但此术语一般指SELECT语句。

  SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。为什么要这样做呢?理解这个概念的最好方法是考察几个例子。


利用子查询进行过滤

订单存储在两个表中。每个订单包含订单编号、客户ID、订单日期,在Orders表中存储为一行。各订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息,只存储顾客ID。顾客的实际信息存储在Customers表中。

现在,假如需要列出订购物品 RGAN01 的所有顾客,应该怎样检索?下面列出具体的步骤。

1. 检索包含物品RGAN01的所有订单的编号。

2. 检索具有前一步骤列出的订单编号的所有顾客的ID。

3. 检索前一步骤返回的所有顾客ID的顾客信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。也可以使用子查询来把3个查询组合成一条语句。

第一条SELECT语句的含义很明确,它对prod_id为RGAN01的所有订单物品,检索其order_num列。输出列出了两个包含此物品的订单:

输入▼

SELECT order_num

FROM OrderItems

WHERE prod_id = 'RGAN01';

输出▼

order_num

-----------

20007

20008 现在,我们知道了哪个订单包含要检索的物品,下一步查询与订单20007和20008相关的顾客ID。利用第5课介绍的IN子句,编写如下的SELECT语句:

输入▼

SELECT cust_id

FROM Orders

WHERE order_num IN (20007,20008);

输出▼

cust_id

----------

1000000004

1000000005现在,结合这两个查询,把第一个查询(返回订单号的那一个)变为子查询。请看下面的SELECT语句:

输入▼

SELECT cust_id

FROM Orders

WHERE order_num IN (SELECT order_num

FROM OrderItems

WHERE prod_id = 'RGAN01');

输出▼

cust_id

----------

1000000004

1000000005分析▼

在SELECT语句中,子查询总是从内向外处理。在处理上面的SELECT语句时,DBMS实际上执行了两个操作。

首先,它执行下面的查询:

SELECT order_num FROM orderitems WHERE prod_id='RGAN01' 此查询返回两个订单号:20007和20008。然后,这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变成:

SELECT cust_id FROM orders WHERE order_num IN (20007,20008) 可以看到,输出是正确的,与前面硬编码WHERE子句所返回的值相同。

提示:格式化SQL

包含子查询的SELECT语句难以阅读和调试,它们在较为复杂时更是如此。如上所示,把子查询分解为多行并进行适当的缩进,能极大地简化子查询的使用。

现在得到了订购物品RGA N01的所有顾客的ID。下一步是检索这些顾客ID的顾客信息。检索两列的SQL语句为:

输入▼

SELECT cust_name,cust_contact

FROM Customers

WHERE cust_id IN ('1000000004','1000000005');可以把其中的WHERE子句转换为子查询,而不是硬编码这些顾客ID:

输入▼

SELECT cust_name,cust_contact

FROM Customers

WHERE cust_id IN (SELECT cust_id

FROM Order

WHERE order_num IN (SELECT order_num

FROM OrderItems

WHERE prod_id = 'RGAN01'));

输出▼

cust_name cust_contact

----------------------------- --------------------

Fun4All Denise L. Stephens

The Toy Store Kim Howard分析▼

为了执行上述SELECT语句,DBMS实际上必须执行三条SELECT语句。最里边的子查询返回订单号列表,此列表用于其外面的子查询的WHERE子句。外面的子查询返回顾客ID列表,此顾客ID列表用于最外层查询的WHERE子句。最外层查询返回所需的数据。

可见,在WHERE子句中使用子查询能够编写出功能很强且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。

警告:只能是单列

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

警告:子查询和性能

这里给出的代码有效,并且获得了所需的结果。但是,使用子查询并不总是执行这类数据检索的最有效方法。


作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示Customers表中每个顾客的订单总数。订单与相应的顾客ID存储在Orders表中。

执行这个操作,要遵循下面的步骤:

1. 从Customers表中检索顾客列表;

2. 对于检索出的每个顾客,统计其在Orders表中的订单数目。

正如前两课所述,可以使用SELECT COUNT(*)对表中的行进行计数,并且通过提供一条WHERE子句来过滤某个特定的顾客ID,仅对该顾客的订单进行计数。例如,下面的代码对顾客1000000001的订单进行计数:

输入▼

SELECT COUNT(*) AS orders

FROM Orders

WHERE cust_id = '1000000001';要对每个顾客执行COUNT(*),应该将它作为一个子查询。请看下面的代码:

输入▼

SELECT cust_name,

cust_state,(SELECT COUNT(*)

FROM Orders

WHERE Orders.cust_id = Customers.cust_id) AS orders

FROM Customers

ORDER BY cust_name;

输出▼

cust_name cust_state orders

------------------------- ---------- ------

Fun4All IN 1

Fun4All AZ 1

Kids Place OH 0

The Toy Store IL 1

Village Toys MI 2分析▼

这条SELECT语句对Customers表中每个顾客返回三列:cust_name、cust_state和orders。orders是一个计算字段,它是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。在此例中,该子查询执行了5次,因为检索出了5个顾客。

子查询中的WHERE子句与前面使用的WHERE子句稍有不同,因为它使用了完全限定列名,而不只是列名(cust_id)。它指定表名和列名(Orders.cust_id和Customers.cust_id)。下面的WHERE子句告诉SQL,比较Orders表中的cust_id和当前正从Customers表中检索的cust_id:

WHERE Orders.cust_id = Customers.cust_id

用一个句点分隔表名和列名,这种语法必须在有可能混淆列名时使用。在这个例子中,有两个cust_id列:一个在Customers中,另一个在Orders中。如果不采用完全限定列名,DBMS会认为要对Orders表中的cust_id自身进行比较。因为

SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id总是返回Orders表中订单的总数,而这个结果不是我们想要的:

输入▼

SELECT cust_name,

(SELECT COUNT(*)

FROM Orders

WHERE cust_id = cust_id) AS orders

FROM Customers

ORDER BY cust_name;

输出▼

cust_name cust_state orders

------------------------- ---------- ------

Fun4All IN 5

Fun4All AZ 5

Kids Place OH 5

The Toy Store IL 5

Village Toys MI 5虽然子查询在构造这种SELECT语句时极有用,但必须注意限制有歧义的列。


0人推荐

推荐文章

热门标签

意见反馈 常见问题 官方微信 返回顶部