子查询进行过滤
各订单的物品的表包含在OrderItems表中,顾客的ID存储在Orders表中,顾客的信息包含在Customers表中。如果要列出订购物品RGAN01的所有顾客,应该怎么检索?
下面给出步骤:
1.检索包含物品RGAN01的所有订单编号
2.检索具有前一步骤列出的订单编号的所有顾客ID
3.检索前一步骤返回的所有顾客ID的顾客信息
我们可以分3个SELECT来查询
SELECT order_num
FROM OrderItems
WHERE order_num='RGAN01';
输出结果是20007,20008.然后找出这两个订单号的顾客ID
SELECT cust_id
FROM Orders
WHERE order_num IN(20007,20008);
现在,结合这两个查询,把第一个查询变为子查询。
SELECT cust_id
FROM Orders
WHERE order_num IN(SELECT order_num FROM OrderItems WHERE prod_id ='RGAN01')
输出结果是一样的。
接下来检索两列的SQL语句为:
SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN(1004,1005);
检索出顾客的信息
同理,可以讲WHERE语句转换为子查询
SELECT 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='RGAN01'));
为了执行上述SELECT语句,必须执行三条SELECT语句, 最里边的查询返回顾客ID列表。
注意:SELECT语句只能查询单个列。
作为计算字段子查询
假如需要显示Customers表中每个顾客的订单总数,订单和相应的顾客ID存储在Orders表中
要执行如下操作:
(1)从Customers表中检索顾客列表
(2)对于检索出的每个顾客,统计其在Orders表中的订单数目
可以通过SELECT COUNT(*)对表中的行计数,并且通过WHERE子句来过滤某个特定的顾客ID,仅对顾客订单进行计数
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表中每个顾客返回三列:cust_name、cust_state和orders。orders是一个计算字段,由圆括号的子查询建立。
注意,本查询中用的WHERE字句略有不同,使用了限定列名,比较Orders表中的cust_id,和Customers表中的cust_id。
WHERE Orders.cust_id=Customers.cust_id
用一个句点分隔表名和列名,在容易混淆列名时必须使用这种语法。这个例子中有两个cust_id,如果不采取限定列明,会混淆。