[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子句中相同数目的列。