11.3 作为计算字段使用子查询

11.3 作为计算字段使用子查询

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

执行这个操作，要遵循下面的步骤：

从Customers表中检索顾客列表；
对于检索出的每个顾客，统计其在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, 
       cust_state,
       (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语句时极有用，但必须注意限制有歧义的列。

警告：完全限定列名
你已经看到了为什么要使用完全限定列名，没有具体指定就会返回错误结果，因为DBMS会误解你的意思。有时候，由于出现冲突列名而导致的歧义性，会引起DBMS抛出错误信息。例如，WHERE或ORDER BY子句指定的某个列名可能会出现在多个表中。好的做法是，如果在SELECT语句中操作多个表，就应使用完全限定列名来避免歧义。

提示：不止一种解决方案
正如这一课前面所述，虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效方法。在后面两课学习JOIN时，我们还会遇到这个例子。