SQL根据另外两列值查询：如何找出数学比物理成绩好的学生姓名

• 该需求属于长表转宽表后对比值的场景，同一名学生的两门科目成绩分属两行，需要先聚合到同一行再做分数比较
• 常用两种实现方案：自连接、条件聚合，执行结果均符合预期仅返回Tom

以下示例默认表名为scores，可根据实际业务表名修改

方案1：自连接实现 #

SELECT t1.Name
FROM scores t1
-- 关联同个学生的物理成绩行
INNER JOIN scores t2 
  ON t1.Name = t2.Name
  AND t1.Subject = 'Math'
  AND t2.Subject = 'Physics'
-- 过滤数学成绩高于物理的记录
WHERE t1.Score > t2.Score;

方案2：条件聚合实现 #

SELECT Name
FROM scores
GROUP BY Name
-- 分组后直接对比同一学生的两门科目成绩
HAVING 
  MAX(CASE WHEN Subject = 'Math' THEN Score END) > 
  MAX(CASE WHEN Subject = 'Physics' THEN Score END);

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