MySQL--分组数据

1、数据分组

#连接数据库
use newschema;
#查看表中数据
select *from products；
#返回供应商1003提供的产品数目
select count(*) as num_prods from products where vend_id=1003;

2、创建分组

select vend_id,count(*) as num_prods from products group by vend_id;

**Group By 子句必须出现在where自居之后，order by 子句之前。

#使用with rollup
select vend_id,count(*) as num_prods from products group by vend_id with rollup;

#使用with rollup关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

 

3、过滤分组

所有类型的where子句都可以用having来替代。唯一差别师where过滤行，而having过滤分组。

select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;

having和where的差别：where在数据分组前进行过滤，having在数据分组后进行过滤。

select vend_id,count(*) as num_prods 
from products 
where prod_price>=10 
group by vend_id 
having count(*)>=2;

分析：where子句过滤所有prod_price至少为10 的行，然后按照cend_id 分组，having子句过滤计数为2或2以上的分组。

 

select vend_id,count(*) as num_prods
 from products 
group by vend_id 
having count(*)>=2;

 4、分组和排序

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;

 

5、select子句顺序

下列表是在使用select语句时必须遵循的次序

select

from

where

group by

having

order by

limit