MySQL如何用单查询按城市与年份分别计算平均值？

嘿，完全不用分开查询！你之前用WHERE IN (SELECT DISTINCT City)的思路不对——这个子句只是帮你过滤出所有城市的数据，但并没有对数据进行分组，所以最终还是会得到全局的平均值，而不是你想要的每个城市+每年的分组结果。

解决这个问题的核心是用GROUP BY子句，把数据按「城市」和「年份」的组合进行分组，然后对每组单独计算平均值。下面给你具体的示例：

假设你的表结构（示例） #

假设你的表名为projects，包含字段：

• City：存储城市名称
• ProjectYear：存储项目的年份（如果是日期字段，后面会有处理方式）
• MetricValue：需要计算平均值的数值字段（比如项目金额、耗时等）

基础SQL查询（已有年份字段） #

SELECT
    City,
    ProjectYear AS Year,
    AVG(MetricValue) AS AnnualAverage
FROM
    projects
GROUP BY
    City, ProjectYear  -- 按城市和年份分组
ORDER BY
    City, Year;  -- 按城市、年份排序，方便查看

如果是日期字段（比如ProjectDate） #

如果你的表中没有单独的年份字段，只有日期字段，需要先提取年份再分组：

SELECT
    City,
    EXTRACT(YEAR FROM ProjectDate) AS Year,  -- 从日期中提取年份
    AVG(MetricValue) AS AnnualAverage
FROM
    projects
GROUP BY
    City, EXTRACT(YEAR FROM ProjectDate)  -- 分组字段要和SELECT里的一致
ORDER BY
    City, Year;

额外注意事项 #

• 检查City字段的一致性：如果城市名称存在空格、大小写差异（比如"北京"和" 北京"，"Shanghai"和"shanghai"），会被当成不同的城市分组。可以用TRIM(City)或者LOWER(City)来统一处理，比如：

  SELECT
      TRIM(LOWER(City)) AS CleanCity,
      ProjectYear AS Year,
      AVG(MetricValue) AS AnnualAverage
  FROM
      projects
  GROUP BY
      TRIM(LOWER(City)), ProjectYear;
  

• 如果需要显示所有城市的所有年份（哪怕某城市某一年没有项目，也要显示平均值为NULL或0），可以先生成一个包含所有城市和所有年份的临时表，再用LEFT JOIN关联你的项目表。不过如果没有这个需求，上面的基础查询就足够满足你的预期了。

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