如何在SQL中对列名相似的行内字段批量求和？

针对手动罗列字段易出错的问题，利用INFORMATION_SCHEMA系统表自动获取列名，再通过动态SQL生成求和查询是最优解，以下分步骤说明：

核心思路 #

通过系统表筛选出所有INPUT_*和OUTPUT_*列名，自动拼接成求和表达式，最终生成并执行分组求和的SQL语句，完全避免手动列字段的失误。

以MySQL为例的具体实现 #

1. 获取INPUT系列字段的拼接字符串

SELECT GROUP_CONCAT(CONCAT('COALESCE(', column_name, ', 0)') SEPARATOR ' + ') INTO @input_cols
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '你的数据库名称'
  AND table_name = '你的目标表名'
  AND column_name LIKE 'INPUT\_%'; -- 下划线加转义符避免模糊匹配误判

2. 获取OUTPUT系列字段的拼接字符串

SELECT GROUP_CONCAT(CONCAT('COALESCE(', column_name, ', 0)') SEPARATOR ' + ') INTO @output_cols
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '你的数据库名称'
  AND table_name = '你的目标表名'
  AND column_name LIKE 'OUTPUT\_%';

3. 生成并执行动态查询

SET @sql = CONCAT(
  'SELECT ID, SUM(', @input_cols, ') AS INPUT_SUM, SUM(', @output_cols, ') AS OUTPUT_SUM ',
  'FROM 你的目标表名 ',
  'GROUP BY ID;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

其他数据库的适配调整 #

• SQL Server/PostgreSQL：用STRING_AGG替代GROUP_CONCAT，比如SQL Server的写法：

SELECT @input_cols = STRING_AGG(CONCAT('COALESCE(', column_name, ', 0)'), ' + ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' -- 注意SQL Server的schema名
  AND TABLE_NAME = '你的目标表名'
  AND COLUMN_NAME LIKE 'INPUT[_]%';

• Oracle：用LISTAGG函数，需要指定分隔符并处理排序：

SELECT LISTAGG(CONCAT('COALESCE(', column_name, ', 0)'), ' + ') WITHIN GROUP (ORDER BY column_name)
INTO input_cols
FROM ALL_TAB_COLUMNS
WHERE OWNER = '你的用户名'
  AND TABLE_NAME = '你的目标表名'
  AND COLUMN_NAME LIKE 'INPUT\_%' ESCAPE '\';

关键注意事项 #

• 加入COALESCE(column_name, 0)是为了处理字段值为NULL的情况，避免NULL导致整个求和结果为NULL；
• 模糊匹配时要给下划线_加转义符，防止把任意单个字符匹配成下划线；
• 执行动态SQL需要对应的数据权限，确保当前用户能访问INFORMATION_SCHEMA（或对应数据库的系统表）。

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