目录
分组去重计数 Group By + Distinct Count
分组条件求和 Group By + Distinct Count + if(case when)
分组条件求和 Group By + Sum + if(case when)
常见聚合函数count、sum、min、max、avg的使用
CommodityPrice表
name | category | amount | weight | unit | price |
---|---|---|---|---|---|
大米 | 粮油 | 1 | 5000 | g | 30 |
花生油 | 粮油 | 1 | 5 | L | 135 |
面条 | 粮油 | 1 | 900 | g | 12 |
洗洁精 | 厨卫 | 1 | 1 | L | 16 |
小蛋糕 | 熟食 | 1 | 200 | g | 10 |
上海青 | 蔬菜 | 1 | 250 | g | 5.5 |
猪肉 | 肉类 | 1 | 300 | g | 16 |
猪肉 | 肉类 | 2 | 800 | g | 35 |
牛肉 | 肉类 | 1 | 300 | g | 40 |
牛肉 | 肉类 | 1 | 500 | g | 60 |
羊肉 | 肉类 | 2 | 300 | g | 55 |
分组 Group By
-- 分组查询category值
select category from CommodityPrice group by category
分组计数 Group By + Count
-- 分组计算每个category的对应记录数
select category, count(1) from CommodityPrice group by category
分组去重计数 Group By + Distinct Count
-- 分组计数每个category下name的非重复数量
select category, count(distinct name) from CommodityPrice
group by category
分组条件求和 Group By + Distinct Count + if(case when)
-- 分组计数每个category下amount<2的name数量
select category,
count(distinct name, if(amount<2, true, null)),
count(distinct name, case when amount>1 then null else name end)
from CommodityPrice group by category
分组求和 Group By + Sum
-- 分组计数每个category下amount总和
select category, sum(amount) from CommodityPrice group by category
分组条件求和 Group By + Sum + if(case when)
-- 分组计数每个category下amount<=1的price总和
select category,
sum(case when amount>1 then 0 else price end),
sum(if(amount<=1, price, 0))
from CommodityPrice group by category
分组筛选
- 满足HAVING 条件的结果将被显示
- HAVING 不能单独使用,必须要跟 group by 一起使用
-- 分组计数每个category下amount<=1的price总和,并筛选出total_price>1的值
select category, sum(if(amount<=1, price, 0)) total_price
from CommodityPrice group by category
having total_price>1
常用5个聚合函数
-- 如下语句中的case when都可以用if(amount<=1, price, null)替代
select category,
sum(case when amount>1 then 0 else price end) total_price,
count(distinct price, case when amount>1 then null else price end),
max(case when amount>1 then null else price end),
min(case when amount>1 then null else price end),
avg(case when amount>1 then null else price end)
from CommodityPrice
group by category
having total_price>1