MySQL學習足跡記錄12--使用子查詢

MySQL學習足跡記錄12--使用子查詢   1.子查詢（subquery):即嵌套在其他查詢中的查詢 原始數據如下：  

   mysql> SELECT order_num FROM orderitems;
+-----------+
| order_num |
+-----------+
|     20005 |
|     20005 |
|     20009 |
|     20005 |
|     20009 |
|     20008 |
|     20006 |
|     20009 |
|     20009 |
|     20005 |
|     20007 |
+-----------+
11 rows in set (0.01 sec)

mysql> SELECT cust_id FROM orders;
+---------+
| cust_id |
+---------+
|   10001 |
|   10001 |
|   10003 |
|   10004 |
|   10005 |
+---------+
5 rows in set (0.01 sec)

現在先分步查詢
step1:
  mysql> SELECT order_num
          -> FROM orderitems
          -> WHERE prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.00 sec)


step2:
 mysql> SELECT cust_id FROM orders
          -> WHERE order_num IN( 20005,20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)


 Step3:
  使用子查詢把step1,step2組合起來（即把20005,20007換掉）
  mysql> SELECT cust_id
           -> FROM orders
           -> WHERE order_num IN( SELECT order_num
           ->                                        FROM orderitems
           ->                                         WHERE prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

TIPS：
  在SELECT語句中，子查詢總是從內向外處理的。
  子查詢可以嵌套多重
 step4:
  mysql> SELECT cust_name,cust_contact
           -> FROM customers
           -> WHERE cust_id IN (10001,10004);      #（10001，10004）既是step3查詢的結果
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.01 sec)

step5:把step4的IN (10001,10004)換成子查詢
 mysql> 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'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

   mysql> SELECT cust_id FROM orders;
+---------+
| cust_id |
+---------+
|   10001 |
|   10001 |
|   10003 |
|   10004 |
|   10005 |
+---------+
5 rows in set (0.01 sec)


mysql> SELECT cust_id FROM customers;
+---------+
| cust_id |
+---------+
|   10001 |
|   10002 |
|   10003 |
|   10004 |
|   10005 |
+---------+
5 rows in set (0.00 sec)

mysql> SELECT cust_id,(SELECT COUNT(*) FROM orders
         ->                                WHERE orders.cust_id = customers.cust_id) AS orders
        -> FROM customers
       -> ORDER BY cust_id;
+---------+--------+              
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
|   10002 |      0 |
|   10003 |      1 |
|   10004 |      1 |
|   10005 |      1 |
+---------+--------+
5 rows in set (0.00 sec)