mysql中union all和WITH ROLLUP实现汇总的两种方式

目录

一、场景需求

二、UNION ALL实现方式

三、WITH ROLLUP实现方式

四、对比总结

一、场景需求

假设存在销售记录表sales：

CREATE TABLE sales (
    sale_date DATE,
    product VARCHAR(50),
    amount DECIMAL(10,2)
);

需要实现：1. 按日统计总销售额 2. 按产品统计总销售额 3. 整体汇总统计

二、UNION ALL实现方式

实现原理

通过多个SELECT语句的集合操作合并结果

-- 按日期汇总
SELECT sale_date, NULL AS product, SUM(amount) AS total
FROM sales
GROUP BY sale_date

UNION ALL

-- 按产品汇总
SELECT NULL, product, SUM(amount)
FROM sales
GROUP BY product

UNION ALL

-- 整体汇总
SELECT NULL, NULL, SUM(amount)
FROM sales;

特点分析:

1. 优点：

可灵活组合不同维度统计

支持跨表联合查询

1. 缺点：

SQL语句冗余

多次全表扫描影响性能

需要手动处理NULL值标识

三、WITH ROLLUP实现方式

实现原理

通过GROUP BY的扩展子句生成层级汇总

SELECT 
    COALESCE(sale_date, '总计') AS sale_date,
    COALESCE(product, '全部产品') AS product,
    SUM(amount) AS total
FROM sales
GROUP BY sale_date, product WITH ROLLUP
HAVING (sale_date IS NOT NULL AND product IS NOT NULL)  -- 排除中间层级
    OR (sale_date IS NULL AND product IS NULL);  -- 仅保留最终汇总

特点分析：

1. 优点：

单次查询完成多级汇总

自动生成层级关系

执行效率更高

1. 缺点：

汇总层级固定（从右向左递减）

NULL值需要特殊处理

无法跨不同分组维度组合

四、对比总结