11 使用子查询
1.子查询
查询(query):任何SQL语句都是查询,但此术语一般指SELECT语句。
子查询(subquery):嵌套在其他查询中的查询。
2.利用子查询进行过滤
假设需要列出订购物品'RGAN01'的所有顾客,检索步骤为:
(1)检索包含物品RGAN01的所有订单的编号;
(2)检索具有前一步骤列出的订单编号的所有顾客的ID;
(3)检索前一步骤返回的所有顾客ID的顾客信息。
输入:
SELECT order_num
FROM OrderItems
WHERE prod_id= 'RGAN01'
输出:
现在知道了包含要检索物品的订单下一步查询与订单20007和20008相关的顾客ID。利用IN子句,输入:
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');
输出:
在SELECT语句中,子查询总是从内向外处理。把子查询分解为多行并进行适当的缩进,能极大地简化子查询的使用。
现在得到了订购订单RGAN01的所有顾客的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 Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id= 'RGAN01'));
输出:
注意:作为子查询的SELECT语句只能查询单个列
3.作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。
假设需要显示Customers表中每个顾客的订单总数。订单与相应的顾客ID存储在Orders表中。
使用SELECT COUNT(*)对顾客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
输出:
此SELECT语句对Customers表中每个顾客返回三列:cust_name、cust_state和orders。子查询中WHERE子句使用了完全限定列名,指定表名和列名,并用一个句点分隔:Orders.cust_id和Customers.cust_id。在有可能混淆列名时必须使用这种语法,若不采用完全限定列名,DBMS会对Orders表中cust_id自身进行比较。
输入:
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE cust_id = cust_id) AS orders
FROM Customers
ORDER by cust_name;
输出: