如何编写SQL查询获取符合特定科目成绩与总分要求的唯一ID

需求说明 #

需要查询满足以下所有条件的唯一ID：

• 数学科目成绩为80分
• 英语科目成绩为70分
• 所有科目总分≥400分

预期结果：ID 1 和 ID 2

数据集 #

ID      Subject Marks
1   Maths   80
1   English 70
1   Hindi   80
1   Science 80
1   SST 90
2   Maths   80
2   English 70
2   Hindi   90
2   Science 80
2   SST 100
3   Maths   80
3   English 60
3   Hindi   90
3   Science 100
3   SST 100

原SQL的问题 #

你之前的SQL使用OR逻辑，会把满足数学80或英语70的ID都筛选出来，且因为是从单条记录匹配，会导致同一个ID重复出现；更关键的是，它无法确保ID同时满足两个科目条件，比如ID3数学是80但英语不是70，也会被错误筛选出来。

正确解法 #

方法一：GROUP BY + HAVING（推荐） #

通过分组后用条件统计来确保两个科目都满足，同时校验总分：

SELECT ID
FROM ff
GROUP BY ID
HAVING 
  -- 确保存在数学80的记录
  SUM(CASE WHEN Subject = 'Maths' AND Marks = 80 THEN 1 ELSE 0 END) > 0
  -- 确保存在英语70的记录
  AND SUM(CASE WHEN Subject = 'English' AND Marks = 70 THEN 1 ELSE 0 END) > 0
  -- 校验总分≥400
  AND SUM(Marks) >= 400;

方法二：JOIN + EXISTS #

通过关联数学和英语的记录来确保同时满足，再校验总分：

SELECT DISTINCT m.ID
FROM ff m
JOIN ff e ON m.ID = e.ID
WHERE 
  m.Subject = 'Maths' AND m.Marks = 80
  AND e.Subject = 'English' AND e.Marks = 70
  AND EXISTS (
    SELECT 1 
    FROM ff t 
    WHERE t.ID = m.ID 
    GROUP BY ID 
    HAVING SUM(Marks) >= 400
  );

两种方法都能得到预期的ID 1和ID 2结果。

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