《MySQL必知必会》学习之 第14章 使用子查询

好好准备，期待新的工作来临。
2020-06-16

使用子查询

作为计算字段使用子查询

 #查找每个顾客的总订单数
#orders中的cust_id与customer中cust_id相等
select cust_name,cust_state,
(select count(*) from orders where orders.cust_id=customers.cust_id) as orders 
from customers order by cust_name;

使用子查询进行过滤

#猜想第一步
select cust_id from orders where order_num in(
     select order_num from orderitems where prod_id='TNT2');
   #返回结果 10001,10004
#猜想第二步
mysql> 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        |
+----------------+--------------+
#与上面的结果一致 
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        |
+----------------+--------------+