MySQL子查询

利用子查询进行过滤：列出订购物品TNT2的所有客户
检索包含物品TNT2的所有订单的编号

select order_num
from orderitems
where prod_id = 'TNT2';

检索前一步列出的订单编号的所有客户的ID

select cust_id
from orders
where order_num in (select order_num
					from orderitems
					where prod_id = 'TNT2');

列出订购物品TNT2的所有客户

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'));

作为计算字段使用子查询：显示customers表中每个客户的订单总数
从customers表中检索客户列表

select count(*) as orders
from orders
where cust_id = 10001;

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

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