MySQL 分组+子查询

#######################
#第十三章 数据分组 
#######################
use test;
show tables;


##创建分组 group by
select vend_id,count(*) as num_prods from products
group by vend_id;
select vend_id,count(*) as num_prods from products
group by vend_id with rollup;


##过滤分组 
#where过滤行 having过滤分组 having支持所有的where操作符 
#where在数据分组前进行过滤 having在数据分组后进行过滤 
select cust_id,count(*) as orders from orders 
group by cust_id
having count(*)>=2;
#看下面的语句 因为price是没有分组的 所以用where ；count（*）是分组后的所以用having 
select vend_id,count(*) as num_prods from products
where prod_price >=10
group by vend_id
having count(*)>=2;


##分组和排序
select order_num,sum(quantity*item_price)as ordertotal from orderitems
group by order_num
having sum(quantity*item_price)>=50;#未排序 
 
select order_num,sum(quantity*item_price)as ordertotal from orderitems
group by order_num
having sum(quantity*item_price)>=50
order by ordertotal;#排序 

########################select的顺序 
#select----from----where----group by----having----order by----limit

#####################
#第十四章 使用子查询 
#####################

#使用子查询进行过滤 
select order_num from orderitems where prod_id='TNT2';
select cust_id from orders where order_num in (20005,20007);

select cust_id from orders 
where order_num in (select order_num from orderitems where prod_id='TNT2');
#注意where的对象一定要和子查询里面select的对象一致 

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 cust_name,cust_state,
       (select count(*) from orders where orders.cust_id=customers.cust_id) as orders
       from customers order by cust_name;