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)
2.计算字段使用子查询
原始数据
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)