MySQL学习足迹记录12--使用子查询

   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)