[MYSQL -14]使用子查詢

查詢： 任何SQL語句都是查詢。但此術語一般指的是SELECT語句。

子查詢：巢狀在其他查詢中的查詢。

select order_num from orderitems where prod_id='TNT2';
select cust_id from orders where order_num in (20005,20007);
-- combaine the two into one
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(select cust_id
                        from orders 
                        where order_num in( select order_num
                                            from orderitems
                                            where prod_id ='TNT2'));

select count(*) as orders from orders where cust_id='10001';
#從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;

select count(*) as orderss  from orders where orders.cust_id=customers.cust_id; -- error,unknown column ´customer.cust_id;´
select count(*) as orderss  from orders where orders.cust_id in (select customers.cust_id from customers);
select cust_id from orders; 
select distinct customers.cust_id from customers;

• 列必須匹配：在WHERE子句中使用子查詢，應該保證SELECT語句具有與WHERE子句中相同數目的列。