MySQL子查询

什么是子查询

子查询（subquery）：子查询即嵌套在其他查询中的查询
用来在多个表中查询过滤信息。

利用子查询进行过滤

案例：
现有表customers， orders， orderitems
customers储存客户信息：

orders储存订单信息：

orderitems储存订单与货物的详细信息：

现在需要查询订购TCNT2的所有客户应该怎样检索？
传统查询：

1. 查询TNCT2的订单编号order_num

select order_num from orderitems where prod_id = 'TNT2';

+-----------+ 
| order_num | 
+-----------+ 
|     20005 | 
|     20007 | 
+-----------+ 
 

2. 通过订单编号order_num查询对应的客户编号cust_id

select cust_id from orders where order_num IN (20005,20007);

+---------+
| cust_id | 
+---------+ 
|   10001 | 
|   10004 | 
+---------+ 

3. 检索前一步骤返回所有客户id的客户信息

 select cust_name,cust_contact from customers where cust_id in (10001,10004); 
 
 +----------------+--------------+ 
 | cust_name      | cust_contact | 
 +----------------+--------------+ 
 | Coyote Inc.    | Y Lee        | 
 | Yosemite Place | Y Sam        | 
 +----------------+--------------+

如果数据量很大使用传统方式查询会很繁琐，我们可以把where自居转化成子查询来返回数据。
子查询：

 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        | 
 +----------------+--------------+

为了执行上述select语句，MySQL实际上必须执行三条SELECT语句。
最里面的子查询返回订单列表，此列表用于其外面的子查询的where语句。
外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的where语句。
最完成查询返回所需数据。

作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。
例：查询customers表中每个客户的订单总数。

1. 从customers中检索客户列表

 select cust_id,cust_name from customers;
 +---------+----------------+ 
 | cust_id | cust_name      | 
 +---------+----------------+ 
 |   10001 | Coyote Inc.    | 
 |   10002 | Mouse House    | 
 |   10003 | Wascals        | 
 |   10004 | Yosemite Place | 
 |   10005 | E Fudd         | 
 +---------+----------------+

2. 对于检索出的每个客户统计其在orders表中的订单数目。

select count（*） 
from orders
where cust_id = '10001';

+--------+ 
| orders | 
+--------+ 
|      2 | 
+--------+

对于每个客户的订单计算应该讲count（）作为子查询

select cust_id,cust_name,    
	(select count(*)     
		from orders          
		where orders.cust_id = customers.cust_id) as orders          
		) 
from customers 
order by cust_name;

+---------+----------------+--------+ 
| cust_id | cust_name      | orders | 
+---------+----------------+--------+ 
|   10001 | Coyote Inc.    |      2 | 
|   10005 | E Fudd         |      1 | 
|   10002 | Mouse House    |      0 | 
|   10003 | Wascals        |      6 | 
|   10004 | Yosemite Place |      1 | 
+---------+----------------+--------+