GROUP BY + UNION ALL 是拼接明细与汇总行的稳妥方案，需确保列数、类型、顺序一致，明细补 NULL 占位，汇总加标识字段，并在外层统一 ORDER BY。

用 GROUP BY + UNION ALL 拼接明细和汇总行

SQL标准并不支持在同一个SELECT里同时查询原始记录和SUM/COUNT。常见的错误写法是 SELECT *, SUM(amount) FROM orders GROUP BY id，这样做要么会在MySQL 5.7+严格模式下报错，要么返回的非聚合字段值不可靠。

稳妥做法是把明细和汇总拆成两部分，再用 UNION ALL 合并。注意必须保证列数、类型、顺序一致：

• 明细部分补占位值：汇总字段用 NULL 或 0 填充，比如 SUM(amount) 对应位置写 NULL
• 汇总部分补标识字段：加一列如 'summary' 字符串，方便前端或后续逻辑区分
• 排序要显式控制：ORDER BY 放在最后，不能写在每个子查询里

SELECT id, amount, 'detail' AS type, NULL AS total_amount
FROM orders
WHERE status = 'paid'
UNION ALL
SELECT NULL AS id, NULL AS amount, 'summary' AS type, SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
ORDER BY type, id;

用窗口函数避免重复扫描（推荐但需版本支持）

要是数据库支持窗口函数（像PostgreSQL 8.4+、SQL Server 2005+、MySQL 8.0+、Oracle 9i+这些），那 SUM() OVER() 可就厉害了，它能在不改变行数的情况下算出全局汇总。跟 UNION ALL 比起来，它更简洁，性能也更好。特别是当基础表特别大，而且WHERE条件还很复杂的时候，它只需要扫描一次就行。

但要注意：窗口函数不能替代 GROUP BY 做分组内汇总；如果要同时显示每组小计和总合计，得嵌套使用或配合 ROLLUP。

• SUM(amount) OVER() 是全表总和，SUM(amount) OVER(PARTITION BY category) 才是按类目分组的小计
• 明细行里混入汇总值后，不能再对这些字段做 GROUP BY，否则语法报错
• 某些旧版 SQLite 或 MariaDB 不支持窗口函数，执行前先查 SELECT version();

SELECT 
id, 
amount, 
category,
SUM(amount) OVER() AS total_all,
SUM(amount) OVER(PARTITION BY category) AS total_by_cat
FROM orders
WHERE status = 'paid';

用 ROLLUP 实现带小计/总计的分层聚合

当需求明确是“分组统计 + 每组小计 + 全局总计”，GROUP BY ... WITH ROLLUP 是最贴近语义的方案。它会自动补出 NULL 行代表更高层级汇总，但需要你主动识别哪些行是汇总行。

关键点在于：ROLLUP 的 NULL 不是数据缺失，而是汇总标记。不同数据库对 NULL 的生成逻辑略有差异（比如 MySQL 在最右列填 NULL，PostgreSQL 可能多列同时为 NULL），所以判断逻辑不能只看单个字段。

• 用 CASE WHEN GROUPING(category) = 1 THEN 'Total' 显式标注汇总行（GROUPING() 函数返回 1 表示该列被 rollup 掉）
• ROLLUP(a,b,c) 会产生 (a,b,c)、(a,b,NULL)、(a,NULL,NULL)、(NULL,NULL,NULL) 四层，顺序固定
• MySQL 8.0+ 支持 GROUPING()，但早期版本只能靠 IS NULL 判断，容易误判真实 NULL 数据

SELECT 
IF(GROUPING(category), 'All Categories', category) AS category,
COUNT(*) AS cnt,
SUM(amount) AS sum_amt
FROM orders
WHERE status = 'paid'
GROUP BY category WITH ROLLUP;

为什么不能用子查询或 CTE 直接拼字段？

有人试图写 SELECT *, (SELECT SUM(amount) FROM orders) AS total FROM orders，看起来能跑通，但这是典型陷阱：子查询会在每一行重复执行，数据量大时性能断崖下跌；更糟的是，如果外层有 WHERE 或 LIMIT，子查询却没同步过滤，结果总数就错了。

CTE（WITH）看似优雅，但多数数据库不会自动优化成单次扫描。比如：

WITH base AS (SELECT * FROM orders WHERE status = 'paid')
SELECT *, (SELECT SUM(amount) FROM base) FROM base;

这段代码在 PostgreSQL 中可能被优化，但在 MySQL 8.0 中仍可能执行两次扫描。真正安全的做法，是把汇总值先算出来存成变量或临时表，再 JOIN 回去——但这已经脱离“一条 SQL”的初衷了。

实际选型时，优先看窗口函数是否可用；不行再用 UNION ALL；只有明确需要多级小计才上 ROLLUP。别为了“看起来简洁”牺牲可读性和可维护性。

《夸克》非常好用的免费AI浏览器

下载APP查看