mysql 分组group by

组合聚集函数

1. 聚集函数是指将 多个数据 通过计算得出 一个值 的函数
2. 组合聚集函数可以通过select 将这些值组合起来
3. as给这些值对应的列命名

select count(*) as num_items,
	min(prod_price) as price_min,
	max(prod_price) as price_max,
	avg(prod_price) as price_avg
from products;

分组 gruop by

1. group by 后必须接实际列或表达式列，不可以接表达式列的别名
2. having 对组合后的数据进行整组的筛选，所以通常having与group by同时出现
3. 出现顺序：group by ,having（接在group by 后），order by(必须最后)

-- # 按照vend_id对count分组
select vend_id,count(*) as num_prod
from products
group by vend_id;

select vend_id, count(*) as num_prod
from products
where prod_price >=4 -- 过滤行级：一行一行过滤
group by vend_id
having count(*) >= 2  -- 过滤组级：一整组过滤
order by vend_id