SQL分组计算平均分：筛选数学平均分高于英语的姓名及查询修正

原查询的问题 #

• 子查询计算的是整个表的数学、英语平均分，不是每个用户各自的科目平均分，完全偏离需求
• 错误使用了不存在的conv_price(price)，表中只有Marks字段，应该用avg(marks)

正确解法一：条件聚合（推荐） #

通过CASE WHEN在分组时分别计算每个用户的数学、英语平均分，再做比较：

SELECT name
FROM xyz_table
WHERE subject IN ('Maths', 'English') -- 仅筛选目标科目，提升查询效率
GROUP BY name
HAVING AVG(CASE WHEN subject = 'Maths' THEN marks END) > AVG(CASE WHEN subject = 'English' THEN marks END)

逻辑说明：

• CASE WHEN会只保留对应科目的分数，其他科目返回NULL，AVG函数会自动忽略NULL值，这样就能得到每个用户单独的数学、英语平均分
• 最终筛选出数学平均分高于英语平均分的用户

正确解法二：子查询关联 #

先通过子查询计算每个用户各科目平均分，再关联筛选：

WITH user_subject_avg AS (
    SELECT 
        name,
        subject,
        AVG(marks) AS avg_marks
    FROM xyz_table
    WHERE subject IN ('Maths', 'English')
    GROUP BY name, subject
)
SELECT m.name
FROM user_subject_avg m
JOIN user_subject_avg e 
  ON m.name = e.name
WHERE m.subject = 'Maths' 
  AND e.subject = 'English'
  AND m.avg_marks > e.avg_marks

验证结果 #

根据你的数据表，最终结果只会返回A（A的数学平均分10，英语平均分6，符合条件；B的两门平均分都是8，不符合；C无目标科目，不纳入）

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