首页 mysql基础教程[basic] 详情

---

介绍了mysql分组聚合的原理，分组聚合的写法

分组统计原理

按照分组字段对数据求和、平均值、计数等操作

• 集合函数：sum/avg/count
• 可以这样理解分组聚合：按照分组字段将数据表拆分为n个小表，每张小表按照聚合字段计算聚合值，如下图所示：

分组聚合实例

-- 聚合
select order_num,count(*) as cnt,sum(quantity) as quan,sum(quantity * item_price) as amt
from orderitems
group by order_num
order by count(*) desc;

-- 聚合后筛选
select order_num,count(*) as cnt,sum(quantity) as quan,sum(quantity * item_price) as amt
from orderitems
group by order_num
having count(*) > 2
order by count(*) desc;

-- 注意 where / having 的区别
-- where 针对的是原表字段进行筛选
-- having 针对的是聚合后的字段进行筛选，如针对sum()/count()/avg() 这些字段进行筛选，having 必须跟在 group by 后面