11 使用子查询

11.1 利用子查询进行过滤

我们用一个例子来展示一下利用子查询进行过滤

现在有3个数据表：orders表，orderitems表和customers表。
订单分别存储在前两个表中。对于包含订单号、客户ID、 订单日期的每个订单，在orders表中存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。
现在，假如需要列出订购物品TNT2的所有客户，具体的步骤为：

检索包含物品TNT2的所有订单的编号
检索具有前一步骤列出的订单编号的所有客户的ID
检索前一步骤返回的所有客户ID的客户信息。

下面按照步骤来编写SQL语句：

第一步：对于prod_id为TNT2的所有订单物品，检索其order_num列。输出列出两个包含此物品的订单：

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

输出结果为：

第二步：查询具有订单20005和20007的客户ID。

SELECT cust_id 
FROM orders
WHERE order_num IN ( 20005,20007 )

输出结果为：

现在将前两步用子查询的方式连接起来：

SELECT cust_id 
FROM orders
WHERE order_num IN ( SELECT order_num
                     FROM orderitems
                     WHERE prod_id = 'TNT2'; )

输出的结果和第二步输出的结果相同：

第三步是检索这些客户ID的客户信息。

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN ( 10001,10004 )

将这三部结合到一起：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN ( SELECT cust_id 
　　　　　　　　　　　 FROM orders
　　　　　　　　　　　 WHERE order_num IN ( SELECT order_num
                                        FROM orderitems
                                        WHERE prod_id = 'TNT2'; ) );

最后的输出结果为：

11.2 作为计算字段使用子查询
使用子查询的另一种方法是创建计算字段。

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
为了执行这个操作，遵循下面的步骤。
　　(1) 从customers表中检索客户列表。
　　(2) 对于检索出的每个客户，统计其在orders表中的订单数目。

例如，下面的代码对客户10001的订单进行计数：

SELECT COUNT(*)
FROM orders
WHERE cust_id =10001；

为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询

综合上述步骤，可以这样写SQL语句

SELECT cust_name,
　　　　cust_state,
　　　　( SELECT COUNT(*)
　　　　　 FROM orders
　　　　　 WHERE orders.cust_id = customers.cust_id ) AS orders
FROM customers
ORDER BY cust_name;

最后的输出结果是：

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