group by

2024年06月12日

柏拉文

越努力，越幸运

一、认识

---

group by 根据一个或者多个列对结果进行分组。在分组的列上我们可以使用count、sum、avg等函数

二、语法

---

根据一列分组,select 查询字段名必须与 group by 字段名一致，且不可出现其他字段，否则不可执行"

select 字段名 函数
from 表名
where 条件 
group by 字段名

根据多列分组,select 查询字段名必须与 group by 字段名一致，且不可出现其他字段,否则不可执行

select 字段名1,字段名2, 函数
from 表名
where 条件 
group by 字段名1,字段名2

三、用法

---

group by 分组，count 统计数据数量

group by 结合 count

select good_id,count("统计") as good_count
from `order`
where good_id != 0
group by good_id;

group by 结合 count 、with rollup、coalesce

select coalesce(good_id,"全部") as good_id,count("统计") as good_count
from `order`
where good_id != 0
group by good_id with rollup;

group by 分组，sum 求数据值和

group by 结合 sum

select good_id,sum(order_num) as good_count 
from `order`
where good_id !=0
group by good_id;

group by 结合 sum、with rollup、coalesce

select coalesce(good_id,"全部") as good_id,sum(order_num) as good_count 
from `order`
where good_id !=0
group by good_id with rollup;