MySQL必知必会学习笔记(5)—— 子查询
1 利用子查询进行过滤
列出订购物品TNT2的所有客户
- 检索包含物品TNT2的所有订单的编号;
- 检索具有前一步骤列出的订单编号的所有客户的ID;
- 检索前一步骤返回的所有客户ID的客户信息;
select order_num
from orderitems
where prod_id='TNT2';
select cust_id
from orders
where order_num IN (20005,20007);
select cust_id
from orders
where order_num IN (
SELECT order_num from orderitems
WHERE prod_id='TNT2');
SELECT cust_name,cust_contact
from customers
where cust_id IN (10001,10004);
2 作为计算字段使用子查询
问题:需要显示 customers 表中每个客户的订单总数。(订单与相应的客户ID存储在orders表中)
- 从 customers 表中检索客户列表;
- 对于检索出的每个客户,统计其在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;