select order_num from orderitems where prod_id='TNT2';select cust_id from orders where order_num in (20005,20007);-- combaine the two into oneselect 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(select cust_id
from orders
where order_num in( select order_num
from orderitems
where prod_id ='TNT2'));selectcount(*) as orders from orders where cust_id='10001';
#从customers表中检索客户列表
#对于检索出的每个客户,统计其在orders表中的订单数目
select cust_name,
cust_state,
(selectcount(*)
from orders
where orders.cust_id=customers.cust_id) as orders
from customers
orderby cust_name;selectcount(*) as orderss from orders where orders.cust_id=customers.cust_id;-- error,unknown column ´customer.cust_id;´selectcount(*) as orderss from orders where orders.cust_id in (select customers.cust_id from customers);select cust_id from orders;selectdistinct customers.cust_id from customers;