如何在SQL中按ID分组计算所有字段的最大平均值？

嘿，我来帮你搞定这个需求！先理清楚两种常见的场景，你可以对照自己的实际情况来用：

场景1：先按ID分组算各字段平均值，再找每个字段的最大平均值 #

如果你的表中同一个ID对应多条记录，想要先算出每个ID下每个字段的平均值，再找出所有ID分组里每个字段的最大平均值，用下面的SQL就可以：

-- 第一步：按ID分组计算每个字段的平均值
WITH id_field_avgs AS (
    SELECT 
        ID,
        AVG(field1) AS avg_field1,
        AVG(field2) AS avg_field2,
        -- 这里依次把field3到field50都按这个格式写出来
        AVG(field50) AS avg_field50
    FROM your_table_name -- 替换成你的表名
    GROUP BY ID
)
-- 第二步：对每个字段的平均值取最大值
SELECT
    MAX(avg_field1) AS max_avg_field1,
    MAX(avg_field2) AS max_avg_field2,
    -- 同样把field3到field50的max_avg_xxx都写出来
    MAX(avg_field50) AS max_avg_field50
FROM id_field_avgs;

要是你的表每个ID只有一条记录，那平均值就是字段本身，这时候直接查每个字段的最大值就行：

SELECT
    MAX(field1) AS max_avg_field1,
    MAX(field2) AS max_avg_field2,
    -- 依次写出所有字段
    MAX(field50) AS max_avg_field50
FROM your_table_name;

场景2：针对每个ID，计算各字段的最大值和平均值 #

如果是想给每个ID单独统计它的每个字段的最大值和平均值，用这个SQL：

SELECT
    ID,
    MAX(field1) AS max_field1,
    AVG(field1) AS avg_field1,
    MAX(field2) AS max_field2,
    AVG(field2) AS avg_field2,
    -- 依次写出所有50个字段的max和avg
    MAX(field50) AS max_field50,
    AVG(field50) AS avg_field50
FROM your_table_name
GROUP BY ID;

小技巧：批量生成字段SQL（避免手动写50个字段） #

50多个字段手动写太麻烦？可以用数据库的元数据自动生成需要的代码。比如在MySQL里，执行下面的语句就能得到所有字段的AVG/MAX语句：

-- 生成AVG语句（用于场景1的CTE部分）
SELECT CONCAT('AVG(', column_name, ') AS avg_', column_name, ',')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '你的数据库名' 
  AND table_name = '你的表名'
  AND column_name != 'ID'; -- 排除ID字段

把生成的结果复制到你的SQL里，删掉最后一行的逗号就搞定了，MAX语句同理，把AVG换成MAX就行。

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