如何用SQL按指定性别分组计算对应平均年龄（MySQL/SQLite）

错误原因 #

1. 子查询仅返回Gender字段，外层查询引用的AnswerText和Answer.QuestionID不存在于子查询结果中，直接触发字段不存在的报错。
2. 原语句未关联同一用户的年龄与性别数据，逻辑上无法实现“按性别分组计算平均年龄”的需求。

解决方案（适配MySQL/SQLite） #

假设你的Answer表包含UserID字段（用于标识每个回答所属的用户，这是关联年龄与性别的核心），提供两种可行写法：

方法1：自连接关联用户数据

SELECT 
  -- 统一性别格式，将大小写/表述不同的同类别性别合并
  CASE 
    WHEN a2.AnswerText IN ('Male', 'male') THEN 'Male'
    WHEN a2.AnswerText IN ('Female', 'female') THEN 'Female'
    WHEN a2.AnswerText IN ('Nonbinary', 'non-binary') THEN 'Nonbinary'
    ELSE a2.AnswerText 
  END AS Gender,
  AVG(a1.AnswerText + 0) AS AverageAge -- 强制将字符串类型的年龄转为数值，避免计算错误
FROM Answer a1
JOIN Answer a2 
  ON a1.UserID = a2.UserID -- 关联同一用户的年龄和性别回答
WHERE 
  a1.QuestionID = 1 -- 筛选年龄问题的数据
  AND a2.QuestionID = 2 -- 筛选性别问题的数据
  AND a2.AnswerText IN ('Male', 'Female', 'male', 'female', '-1', 'Nonbinary', 'non-binary') -- 目标性别范围
GROUP BY 
  CASE 
    WHEN a2.AnswerText IN ('Male', 'male') THEN 'Male'
    WHEN a2.AnswerText IN ('Female', 'female') THEN 'Female'
    WHEN a2.AnswerText IN ('Nonbinary', 'non-binary') THEN 'Nonbinary'
    ELSE a2.AnswerText 
  END;

方法2：条件聚合（无需自连接）

SELECT 
  CASE 
    WHEN Gender IN ('Male', 'male') THEN 'Male'
    WHEN Gender IN ('Female', 'female') THEN 'Female'
    WHEN Gender IN ('Nonbinary', 'non-binary') THEN 'Nonbinary'
    ELSE Gender 
  END AS Gender,
  AVG(Age + 0) AS AverageAge
FROM (
  -- 先按用户分组，提取每个用户的年龄和性别
  SELECT 
    UserID,
    MAX(CASE WHEN QuestionID = 1 THEN AnswerText END) AS Age,
    MAX(CASE WHEN QuestionID = 2 THEN AnswerText END) AS Gender
  FROM Answer
  WHERE 
    QuestionID IN (1, 2) -- 仅处理年龄和性别问题
    -- 过滤符合要求的性别数据
    AND (QuestionID != 2 OR AnswerText IN ('Male', 'Female', 'male', 'female', '-1', 'Nonbinary', 'non-binary'))
  GROUP BY UserID
  HAVING Age IS NOT NULL -- 排除没有提供年龄的用户
) AS UserAnswers
GROUP BY 
  CASE 
    WHEN Gender IN ('Male', 'male') THEN 'Male'
    WHEN Gender IN ('Female', 'female') THEN 'Female'
    WHEN Gender IN ('Nonbinary', 'non-binary') THEN 'Nonbinary'
    ELSE Gender 
  END;

注意事项 #

如果你的Answer表没有UserID或类似的用户唯一标识字段，将无法关联同一用户的年龄与性别回答，此时需要先确认表结构是否缺失关键字段，否则无法正确计算每类性别的平均年龄。

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