如何查询SQL中所有子分类的总计_使用嵌套查询配合ROLLUP扩展-SQL-PHP中文网

rollup不能直接用于子分类层级聚合，需先用递归cte或自连接展开树形结构为扁平化路径，再对层级字段（如level1_name、path）执行group by ... with rollup，以生成含小计和总计的多级汇总结果。

如何查询sql中所有子分类的总计_使用嵌套查询配合rollup扩展

ROLLUP 不能直接用于子分类层级聚合，得先构造层级关系

SQL 的 GROUP BY ... WITH ROLLUP 只按字段顺序做前缀式汇总（比如 (a,b,c) 会生成 (a,b,c)、(a,b,NULL)、(a,NULL,NULL)、(NULL,NULL,NULL)），它不理解“子分类属于父分类”这种树状结构。想查“所有子分类的总计”，必须先明确哪些是子分类——这需要递归查询或预展开的层级表。

常见错误是直接对 category_id 和 parent_id 用 ROLLUP，结果得到一堆 NULL 行，毫无业务意义。

如果你的数据在 MySQL 8.0+ 或 PostgreSQL，优先用 WITH RECURSIVE 构建完整路径（如 /1/5/12）再分组
如果用的是 MySQL 5.7 或 SQL Server 2016 以前版本，得靠自连接或临时表把子分类逐级打平
ROLLUP 真正起作用的地方，是在“已展开的扁平化层级字段”上，比如 level1_name、level2_name、level3_name

用自连接展开子分类（兼容 MySQL 5.7 / SQL Server）

假设你有表 categories（含 id、name、parent_id）和销售表 sales（含 category_id、amount）。要统计每个分类及其所有后代的销售总额，就得把每条销售记录映射到它所属的所有上级分类。

核心思路：对每条销售记录，找出其 category_id 对应的所有祖先（包括自己），然后按这些祖先分组求和。

SELECT c.name, SUM(s.amount) AS total
FROM sales s
JOIN categories c ON s.category_id = c.id
JOIN categories c2 ON c2.id = s.category_id OR c2.id IN (
  SELECT parent_id FROM categories WHERE id = s.category_id
  UNION ALL
  SELECT parent_id FROM categories WHERE id IN (
    SELECT parent_id FROM categories WHERE id = s.category_id
  )
  -- 实际中建议用存储过程或临时表替代多层子查询
)
GROUP BY c.name WITH ROLLUP;

⚠️ 这种写法在数据量大时极慢。更可行的是预先生成一张 category_ancestors 表，字段为 category_id、ancestor_id、depth，用一次递归或应用层补全，之后关联就快了。

MySQL 8.0+ 正确用法：CTE + ROLLUP 分层统计

先用 WITH RECURSIVE 把树展开成父子链，再用 GROUP BY ... WITH ROLLUP 做跨层级汇总。关键不是对原始 ID 汇总，而是对“路径段”汇总。

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, CAST(name AS CHAR(500)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, CONCAT(t.path, ' > ', c.name)
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT 
  IFNULL(t1.name, '总计') AS category,
  SUM(s.amount) AS total
FROM tree t1
JOIN sales s ON s.category_id = t1.id
GROUP BY t1.name WITH ROLLUP;

注意：WITH ROLLUP 在这里只给最顶层分类加一行总计；若想让每个父类都包含其子类合计，需改用 GROUP BY t1.path WITH ROLLUP，再用 SUBSTRING_INDEX 截出最后一级名。

ROLLUP 的汇总行永远在分组结果末尾，且 NULL 值出现在对应分组字段位置，别用 WHERE name IS NULL 过滤，要用 HAVING 或外层 CASE
MySQL 中 WITH ROLLUP 与 ORDER BY 共存时，排序发生在汇总前，可能导致总计行不在最后——加 ORDER BY ... IS NULL DESC 强制置底
PostgreSQL 不支持 WITH ROLLUP，得用 GROUPING SETS ((a), (a,b), ()) 替代