MySQL 8.0如何统计列值并生成对应列名实现分组聚合计算

适用环境：MySQL Ver 8.0.29-0

核心逻辑 #

通过条件聚合直接完成行转列计算，一次关联扫描即可得到结果，无对应类型的计数、占比自动填充0，不需要额外依赖插件或复杂语法。

直接可用SQL（性能最优版） #

不需要先按name、type做二次分组，直接在两表关联后按name维度分组统计：

SELECT
  a.name AS address,
  -- 统计三类type对应的计数，无匹配时返回0
  SUM(CASE WHEN b.type = 1 THEN a.count ELSE 0 END) AS count1,
  SUM(CASE WHEN b.type = 2 THEN a.count ELSE 0 END) AS count2,
  SUM(CASE WHEN b.type = 3 THEN a.count ELSE 0 END) AS count3,
  -- 统计当前name分组下的总计数
  SUM(a.count) AS total,
  -- 计算对应占比，保留4位小数，自动处理除以0异常，无匹配返回0
  ROUND(IFNULL(SUM(CASE WHEN b.type = 1 THEN a.count ELSE 0 END) / NULLIF(SUM(a.count), 0), 0), 4) AS ratio1,
  ROUND(IFNULL(SUM(CASE WHEN b.type = 2 THEN a.count ELSE 0 END) / NULLIF(SUM(a.count), 0), 0), 4) AS ratio2,
  ROUND(IFNULL(SUM(CASE WHEN b.type = 3 THEN a.count ELSE 0 END) / NULLIF(SUM(a.count), 0), 0), 4) AS ratio3
FROM tableA a
INNER JOIN tableB b
  ON a.uid = b.uid
GROUP BY a.name;

兼容已有聚合逻辑的写法 #

如果你已经完成了按a.name、b.type分组求和的基础SQL，可以嵌套一层做二次聚合，不需要改动原有已验证的逻辑：

SELECT
  name AS address,
  IFNULL(SUM(CASE WHEN type = 1 THEN type_count ELSE 0 END), 0) AS count1,
  IFNULL(SUM(CASE WHEN type = 2 THEN type_count ELSE 0 END), 0) AS count2,
  IFNULL(SUM(CASE WHEN type = 3 THEN type_count ELSE 0 END), 0) AS count3,
  SUM(type_count) AS total,
  ROUND(IFNULL(SUM(CASE WHEN type = 1 THEN type_count ELSE 0 END) / NULLIF(SUM(type_count), 0), 0), 4) AS ratio1,
  ROUND(IFNULL(SUM(CASE WHEN type = 2 THEN type_count ELSE 0 END) / NULLIF(SUM(type_count), 0), 0), 4) AS ratio2,
  ROUND(IFNULL(SUM(CASE WHEN type = 3 THEN type_count ELSE 0 END) / NULLIF(SUM(type_count), 0), 0), 4) AS ratio3
FROM (
  -- 此处替换为你已经编写完成的按name、type分组聚合的SQL即可
  SELECT 
    a.name, 
    b.type, 
    SUM(a.count) AS type_count
  FROM tableA a
  INNER JOIN tableB b ON a.uid = b.uid
  GROUP BY a.name, b.type
) t
GROUP BY name;

注意事项 #

• 占比字段默认返回0~1之间的4位小数，如果需要百分比格式，可以将占比计算部分修改为CONCAT(ROUND(计算逻辑,4)*100,'%')
• NULLIF(SUM(..),0)用于规避总计数为0时的除以0报错，该场景下占比自动返回0，符合填充要求
• 如果后续type枚举值新增，只需要对应增加同规则的count、ratio字段即可，逻辑不需要调整

内容的提问来源于stack exchange，提问作者eastar pen