SQLite中按不同QuestionID分组计算平均年龄的实现问题

你的核心问题是要关联同一用户的年龄（QuestionID=1）和性别（QuestionID=2）回答，才能按指定性别分组计算平均年龄。原SQL的错误在于子查询仅保留了性别数据，无法获取年龄信息，且外层筛选条件逻辑矛盾。

以下是两种可行的解决方案：

方案一：自连接关联用户数据 #

假设你的Answer表包含UserID字段（用于标识同一用户的不同回答），通过自连接将同一用户的年龄和性别数据关联：

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(CAST(a1.AnswerText AS INTEGER)) 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 Gender;

方案二：使用CTE条件聚合 #

先按用户分组提取每个用户的年龄和性别，再按性别计算平均年龄：

WITH UserResponses AS (
  SELECT 
    UserID,
    -- 提取当前用户的年龄回答
    MAX(CASE WHEN QuestionID = 1 THEN AnswerText END) AS Age,
    -- 提取当前用户的性别回答
    MAX(CASE WHEN QuestionID = 2 THEN AnswerText END) AS GenderRaw
  FROM Answer
  WHERE 
    QuestionID IN (1, 2)
    AND (QuestionID != 2 OR GenderRaw IN ('Male', 'Female', 'male', 'female', '-1', 'Nonbinary', 'non-binary'))
  GROUP BY UserID
)
SELECT 
  -- 统一性别格式
  CASE 
    WHEN GenderRaw IN ('Male', 'male') THEN 'Male'
    WHEN GenderRaw IN ('Female', 'female') THEN 'Female'
    WHEN GenderRaw IN ('Nonbinary', 'non-binary') THEN 'Nonbinary'
    ELSE GenderRaw 
  END AS Gender,
  AVG(CAST(Age AS INTEGER)) AS AverageAge
FROM UserResponses
WHERE Age IS NOT NULL -- 过滤未填写年龄的用户
GROUP BY Gender;

原SQL错误说明 #

1. 子查询仅返回了性别字段Gender，外层查询试图引用AnswerText（年龄）但该字段不存在于子查询结果中，导致报错“no such column: AnswerText”。
2. 外层的WHERE Answer.QuestionID = 1逻辑无效，因为子查询已经筛选出QuestionID=2的记录，无法再获取QuestionID=1的数据。

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