使用子查询

关于子查询使用

利用子查询进行过滤

列出够物品RGAN01的所有客户。

  • 检索包含物品RGAN01的所有订单编号。
  • 检索具有前一步骤列出的订单编号所有客户ID。
  • 检索前一步骤返回的所有客户ID的客户信息。
    1. mysql> SELECT order_num
    2.   -> FROM OrderItems
    3.   -> WHERE prod_id = 'RGAN01';
    4. +-----------+
    5. | order_num |
    6. +-----------+
    7. |     20007 |
    8. |     20008 |
    9. +-----------+
    10. 2 rows in set (0.00 sec)

解释: 列出所有RGAN01订单物品

  1. mysql> SELECT cust_id
  2.     -> FROM Orders
  3.     -> WHERE order_num IN (20007, 20008);
  4. +------------+
  5. | cust_id    |
  6. +------------+
  7. | 1000000004 |
  8. | 1000000005 |
  9. +------------+
  10. 2 rows in set (0.00 sec)

把上面两个查询组合成子查询

  2. mysql> SELECT cust_id
  3.     -> FROM Orders
  4.     -> WHERE order_num IN (SELECT order_num
  5.     -> FROM OrderItems
  6.     -> WHERE prod_id = 'RGAN01');
  7. +------------+
  8. | cust_id    |
  9. +------------+
  10. | 1000000004 |
  11. | 1000000005 |
  12. +------------+
  13. 2 rows in set (0.00 sec)
  • 子查询是从内向外处理
  • 先执行 SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'
  • 把返回的订单号，20007，20008两个值以IN操作符用逗号格式传递给外部查询，
  • 再用SELECT cust_id FROM orders WHERE order_num IN (20007,20008)
    检索客户的ID
  1. mysql> SELECT cust_name, cust_contact
  2.     -> FROM Customers
  3.     -> WHERE cust_id IN (SELECT cust_id
  4.     -> FROM Orders
  5.     -> WHERE order_num IN (SELECT order_num
  6.     -> FROM OrderItems
  7.     -> WHERE prod_id = 'RGAN01'));
  8. +---------------+--------------------+
  9. | cust_name     | cust_contact       |
  10. +---------------+--------------------+
  11. | Fun4All       | Denise L. Stephens |
  12. | The Toy Store | Kim Howard         |
  13. +---------------+--------------------+
  14. 2 rows in set (0.01 sec)
  • 解释:
    • SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01' 查询返回订单列表
    • 把上面的查询结果，给予 SELECT cust_id FROM Orders， 返回客户ID
    • 拿到返回的客户ID给予外层WHERE子句查询。
      注意: 子查询只能返回单个列。

作为计算字段使用子查询

例子：计算Customers中每个客户订单总数。

  • 从Customers 表中检索客户列表
  • 对检索出来的每个客户，统计其在Orders表中的订单数目。
    单个客户查询
  1. mysql> SELECT COUNT(*) AS orders
  2.     -> FROM Orders
  3.     -> WHERE cust_id = '1000000001';
  4. +--------+
  5. | orders |
  6. +--------+
  7. |      2 |
  8. +--------+
  9. 1 row in set (0.00 sec)

对每个客户执行COUNT(*) 

  1. mysql> SELECT cust_name,
  2.     -> cust_state,
  3.     -> (SELECT COUNT(*)
  4.     -> FROM Orders
  5.     -> WHERE Orders.cust_id = Customers.cust_id) AS
  6.     -> orders
  7.     -> FROM Customers
  8.     -> ORDER BY cust_name;
  9. +---------------+------------+--------+
  10. | cust_name     | cust_state | orders |
  11. +---------------+------------+--------+
  12. | Fun4All       | IN         |      1 |
  13. | Fun4All       | AZ         |      1 |
  14. | Kids Place    | OH         |      0 |
  15. | The Toy Store | IL         |      1 |
  16. | Village Toys  | MI         |      2 |
  17. +---------------+------------+--------+
  18. 5 rows in set (0.01 sec)

解释: 对Customers表返回三列，cust_name, cust_state,orders.orders 是计算字段，由 (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) 建立。每检索一个客户，执行一次计算，

Orders.cust_id = Customers.cust_id , 其中的句号，表示指定限定表名跟列，如果不具体指定表名，列名，将返回Orders 表中的订单总数。如下:

  1. mysql> SELECT cust_name,
  2.     -> cust_state,
  3.     -> (SELECT COUNT(*)
  4.     -> FROM Orders
  5.     -> WHERE cust_id = cust_id) AS orders
  6.     -> FROM Customers
  7.     -> ORDER BY cust_name;
  8. +---------------+------------+--------+
  9. | cust_name     | cust_state | orders |
  10. +---------------+------------+--------+
  11. | Fun4All       | IN         |      5 |
  12. | Fun4All       | AZ         |      5 |
  13. | Kids Place    | OH         |      5 |
  14. | The Toy Store | IL         |      5 |
  15. | Village Toys  | MI         |      5 |
  16. +---------------+------------+--------+
  17. 5 rows in set (0.00 sec)

原文: https://cxiaodian.gitbooks.io/mysql/content/chapter10.html