子查询:嵌套在其他查询中的查询,举例分析吧
1、查询出订购购物品TNT2的所有客户ID
(1)查包含TNT2的所有订单编号20005和20007
select order_num from orderitems where order_name = 'TNT2';
(2)查出具有订单号的客户
select cust_id from orders where order_num IN (20005,20007)
(3) 整合一下:
select cust_id from orders where order_num
IN (select order_num from orderitems where order_name = 'TNT2')
2、使用子查询的另一种方法是创建计算字段。
例如要显示customers表中每个客户的订单总数。订单与相应的客户ID存在order表中。
1、从customers表中找出客户列表。
select cust_id from customers
2、对于检索出来的具体用户,统计其在order表中的订单数目
SELECT COUNT(*) as num_prods FROM orders where cust_id=10001
组合:
select cust_name,
(
SELECT COUNT(*) FROM orders WHERE orders.cust_id= customers.cust_id
) as ordertotal
from customers ORDER BY cust_name