如何在MySQL中批量计算多列平均值并插入新表？

手动写30多列的AVG确实太繁琐了，先帮你纠正下原SQL的语法问题，再给你一个自动处理所有列的优雅方案。

首先，你的原SQL存在几个语法错误：

• INSERT INTO的列名应该用一个括号包裹所有列，而不是每个列单独加括号
• 子查询里只有第一列加了AVG，其他列都没加，这样没法计算平均值
• 表名不能用单引号'FaceDistancesHappy'，应该用反引号或者直接写

修正后的手动写法大概是这样（但还是不推荐，因为列太多）：

INSERT INTO FaceAverages(`rightEyeRightUpper`, `rightEyeLeftUpper`, `rightEyeRightLower`, `rightEyeLeftLower`, `leftEyeRightUpper`)
SELECT AVG(`rightEyeRightUpper`), AVG(`rightEyeLeftUpper`), AVG(`rightEyeRightLower`), AVG(`rightEyeLeftLower`), AVG(`leftEyeRightUpper`)
FROM `FaceDistancesHappy`;

自动处理所有列的动态SQL方案 #

既然有30多列，用动态SQL自动生成语句才是高效的方式，不需要手动列所有字段。下面是具体实现：

-- 1. 获取FaceDistancesHappy表所有列的AVG表达式
SET @avg_cols = (
  SELECT GROUP_CONCAT(CONCAT('AVG(`', column_name, '`)'))
  FROM information_schema.COLUMNS
  WHERE table_schema = DATABASE()  -- 当前数据库
    AND table_name = 'FaceDistancesHappy'
    -- 如果有不需要计算平均值的列，可以在这里过滤，比如：AND column_name NOT IN ('id', 'create_time')
);

-- 2. 获取FaceAverages表的所有目标列名
SET @target_cols = (
  SELECT GROUP_CONCAT(`column_name`)
  FROM information_schema.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'FaceAverages'
    -- 同样，如果有不需要插入的列（比如自增主键），这里过滤
);

-- 3. 拼接完整的INSERT语句
SET @sql = CONCAT(
  'INSERT INTO FaceAverages (', @target_cols, ') SELECT ', @avg_cols, ' FROM FaceDistancesHappy;'
);

-- 4. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键注意事项 #

• 确保两张表的列名完全对应，数量和数据类型也要匹配，否则插入会报错
• 如果GROUP_CONCAT返回的字符串太长（列特别多的情况），可以先临时调整长度：SET SESSION group_concat_max_len = 1000000;
• 如果你的程序是C#这类语言，直接把这段动态SQL作为command.CommandText执行即可，不需要拆分步骤

内容的提问来源于stack exchange，提问作者the pro-gramer