18、MySQL的子查询（嵌套查询）二层过滤

1、子查询，找出所有物品的订单2005,2007

根据不同的订单号码，找出相对应的客户，

传统模式

mysql> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 | 5.99       |
|     20005 |          2 | ANV02   |        3 | 9.99       |
|     20005 |          3 | TNT2    |        5 | 10.00      |
|     20005 |          4 | FB      |        1 | 10.00      |
|     20006 |          1 | JP2000  |        1 | 55.00      |
|     20007 |          1 | TNT2    |      100 | 10.00      |
|     20008 |          1 | FC      |       50 | 2.50       |
|     20009 |          1 | FB      |        1 | 10.00      |
|     20009 |          2 | OL1     |        1 | 8.99       |
|     20009 |          3 | SLING   |        1 | 4.49       |
|     20009 |          4 | ANV03   |        1 | 14.99      |
+-----------+------------+---------+----------+------------+
11 rows in set (0.08 sec)

mysql> select * from orderitems where prod_id = 'TNT2';
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          3 | TNT2    |        5 | 10.00      |
|     20007 |          1 | TNT2    |      100 | 10.00      |
+-----------+------------+---------+----------+------------+
2 rows in set (0.05 sec)

mysql> select * from orders where order_num in (20005, 20007);
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
+-----------+---------------------+---------+

子查询模式 子查询解决多句语句的问题

mysql> select * from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
+-----------+---------------------+---------+

子查询作为计算字段使用，可以放在where前面

mysql> select * from orders;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+
5 rows in set (0.06 sec)

mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+

mysql> select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) from customers;
+----------------+------------+------------------------------------------------------------------------+
| cust_name      | cust_state | (select count(*) from orders where orders.cust_id = customers.cust_id) |
+----------------+------------+------------------------------------------------------------------------+
| Coyote Inc.    | MI         |                                                                      2 |
| Mouse House    | OH         |                                                                      0 |
| Wascals        | IN         |                                                                      1 |
| Yosemite Place | AZ         |                                                                      1 |
| E Fudd         | IL         |                                                                      1 |
+----------------+------------+------------------------------------------------------------------------+