SQL子查询未按预期运行：按学历分组的各婚姻状态收入统计值相同

问题根因 #

你写的SQL返回所有学历对应统计值完全一致，核心原因有两个：

• 所有嵌套子查询未关联外层的education分组字段：内层子查询的过滤逻辑只限制了婚姻状态、出生年份、收入条件，没有加education = 外层当前行的education的关联条件，每个子查询统计的都是全表符合要求的全局值，和外层分组的学历无关，所以所有学历行拿到的统计结果完全相同。
• 冗余的DISTINCT关键字：外层查询已经使用GROUP BY education做分组去重，额外加DISTINCT属于无效逻辑，不会影响结果但会增加不必要的性能开销。

优化实现方案 #

推荐使用条件聚合的写法替代多子查询逻辑，只需一次表扫描即可完成统计，性能更高，也能避免子查询关联错误：

SELECT 
    education,
    MIN(CASE WHEN marital_status = 'Married' THEN CONVERT(INT, income) END) AS Min_Married,
    AVG(CASE WHEN marital_status = 'Married' THEN CONVERT(INT, income) END) AS Avg_Married,
    MAX(CASE WHEN marital_status = 'Married' THEN CONVERT(INT, income) END) AS Max_Married,
    MIN(CASE WHEN marital_status = 'Single' THEN CONVERT(INT, income) END) AS Min_Single,
    AVG(CASE WHEN marital_status = 'Single' THEN CONVERT(INT, income) END) AS Avg_Single,
    MAX(CASE WHEN marital_status = 'Single' THEN CONVERT(INT, income) END) AS Max_Single,
    MIN(CASE WHEN marital_status = 'together' THEN CONVERT(INT, income) END) AS Min_Together,
    AVG(CASE WHEN marital_status = 'together' THEN CONVERT(INT, income) END) AS Avg_Together,
    MAX(CASE WHEN marital_status = 'together' THEN CONVERT(INT, income) END) AS Max_Together
FROM [dbo].[marketing_campaign]
WHERE 
    year_birth > 1980 
    AND income <> 0
    AND education IN ('master', 'Graduation', 'phd', 'basic')
GROUP BY education
ORDER BY education DESC

逻辑说明 #

• 先通过WHERE子句统一过滤出符合「出生年份大于1980、收入不为0、学历在指定范围」的所有有效数据
• 按education分组后，通过CASE WHEN在聚合函数内判断婚姻状态，分别统计不同婚姻状态对应的收入极值和均值
• 聚合函数会自动忽略CASE WHEN不匹配返回的NULL值，不会影响统计结果准确性

内容的提问来源于stack exchange，提问作者Martin Nordin Sundberg