MySQL中WITH ROLLUP的具体使用
19人参与 • 2025-07-17 • Mysql
今天第一次知道有这个用法，记录一下吧
with rollup 是 mysql 中一个非常实用的 group by 扩展功能，用于生成分层汇总报表。它确实不像基础 sql 语句那样常见，但在数据分析场景中非常强大。
1. rollup 的基本功能
with rollup 会在 group by 分组结果的基础上：
- 为每个分组层级添加小计行
- 最后添加一个总计行
- 生成的汇总行中，被汇总的列显示为 null
2. 实际案例解析
假设有销售数据表 sales_data：
| category | region | sales | |----------|---------|-------| | 电子产品 | 华北 | 1000 | | 电子产品 | 华东 | 1500 | | 家居用品 | 华北 | 800 | | 家居用品 | 华南 | 1200 |
执行你的示例查询：
select
ifnull(category, 'all categories') as category,
ifnull(region, 'all regions') as region,
sum(sales) as total_sales
from sales_data
group by category, region with rollup;
结果将是：
| category | region | total_sales | |----------------|-------------|-------------| | 电子产品 | 华北 | 1000 | | 电子产品 | 华东 | 1500 | | 电子产品 | all regions | 2500 | ← 电子产品小计 | 家居用品 | 华北 | 800 | | 家居用品 | 华南 | 1200 | | 家居用品 | all regions | 2000 | ← 家居用品小计 | all categories | all regions | 4500 | ← 总计行
3. rollup 的层级关系
对于 group by a, b, c with rollup，它会生成：
- 基础分组 (a, b, c)
- 一级小计 (a, b, null)
- 二级小计 (a, null, null)
- 总计 (null, null, null)
4. 实际应用场景
(1) 销售报表分析
select
year(order_date) as year,
quarter(order_date) as quarter,
month(order_date) as month,
sum(amount) as revenue
from orders
group by year(order_date), quarter(order_date), month(order_date)
with rollup;
(2) 库存分类统计
select
warehouse,
product_type,
count(*) as item_count,
sum(quantity) as total_quantity
from inventory
group by warehouse, product_type with rollup;
5. 高级用法技巧
(1) 识别汇总行
select
category,
region,
sum(sales) as total_sales,
grouping(category) as is_category_summary,
grouping(region) as is_region_summary
from sales_data
group by category, region with rollup;
(2) 多维度交叉分析
select
ifnull(category, 'total') as category,
sum(case when region='华北' then sales end) as north,
sum(case when region='华东' then sales end) as east,
sum(sales) as subtotal
from sales_data
group by category with rollup;
6. 与其他数据库的对比
| 功能 | mysql | sql server | oracle | postgresql |
|---|---|---|---|---|
| with rollup | ✓ | ✗ | ✗ | ✗ |
| grouping sets | ✗ | ✓ | ✓ | ✓ |
| cube | ✗ | ✓ | ✓ | ✓ |
在不支持 rollup 的数据库中，可以使用 union all 组合多个查询来模拟。
7. 性能注意事项
- rollup 会在服务器端生成额外的汇总行
- 大数据集时可能影响性能
- 考虑在应用层实现类似逻辑（特别是web分页时）
8. 为什么你可能没见过？
- 业务场景限制：常规crud操作不需要
- 替代方案：有些团队用应用代码计算汇总
- 报表工具：bi工具通常内置了汇总功能
- 新版本特性：不是所有开发者都熟悉较新的sql功能
rollup 特别适合需要生成：
✅ 分类小计报表
✅ 多层汇总统计
✅ 财务或销售分析报表
下次需要做分层汇总时，可以尝试使用这个强大的功能。
案例：sql175 有取消订单记录的司机平均评分
select
coalesce(driver_id, '总体') as driver_id,
round(avg(grade), 1) as grade
from
tb_get_car_order
where
driver_id in (
select
driver_id
from
tb_get_car_record
join tb_get_car_order using (order_id)
where
year(order_time) = 2021
and month(order_time) = 10
and start_time is null
)
and grade is not null
group by
driver_id
with
rollup
order by
driver_id is null,
driver_id
sql 笔记：with rollup 和 order by 的配合使用
问题背景
当使用 with rollup 生成汇总行时，汇总行的分组列值为 null。如果我们想给这个 null 值赋予一个有意义的名称（如"总体"或"总计"），并在排序时确保这一行显示在最后，需要注意一些技巧。
解决方案
1. 使用 coalesce 或 ifnull 重命名汇总行
select
coalesce(driver_id, '总体') as driver_id,
round(avg(grade), 1) as grade
from ...
group by driver_id with rollup
2. 正确排序确保汇总行在最后
当添加 order by 时，简单的按列排序会导致"总体"行按字母顺序排列，而不是显示在最后。解决方案：
方法一：利用 null 值排序特性
order by driver_id is null, driver_id
• driver_id is null：对于汇总行返回 1，其他行返回 0
• 这样汇总行会排在最后，其他行按 driver_id 排序
方法二：使用 case 表达式
order by case when driver_id is null then 1 else 0 end, driver_id
• 更显式地控制排序优先级
关键点
- with rollup 生成的汇总行的分组列值为 null
- 使用 coalesce/ifnull 可以美化显示这个 null 值
- 排序时需要特殊处理才能确保汇总行在最后
- order by column is null 是一个简洁有效的解决方案
到此这篇关于mysql中with rollup的具体使用的文章就介绍到这了,更多相关mysql with rollup内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
赞 (0)
您想发表意见！！点此发布评论
发表评论