在SQL中查找具有最大值的记录

Question

我有下表：

Class, Name, Score
1, Anna, 34
1, Andy, 80
2, Brooke, 90
2, Brad, 70
3, Charles, 67
3, Christina, 66

如何在每个“班级”中找到最大“分数”的“名称”？

要求的输出：

Class, Name, Score
1, Andy, 80
2, Brooke, 90
3, Charles, 67

这是针对MySQL的。

Answer 1

WITH ClassScores AS
(
    SELECT 1 AS class, 'Anna' AS name, 34 AS score 
    UNION
    SELECT 1, 'Andy', 80  
    UNION
    SELECT 2, 'Brooke', 90  
    UNION
    SELECT 2, 'Brad', 70  
    UNION
    SELECT 3, 'Charles', 67  
    UNION
    SELECT 3, 'Christina', 66 
)

SELECT C1.Class, C1.Name, C1.Score
  FROM ClassScores AS C1
  JOIN (SELECT Class, MAX(Score) AS MaxScore
          FROM ClassScores
         GROUP BY Class
       ) AS C2
    ON C1.Class = C2.Class
   AND C1.Score = C2.MaxScore
 ORDER BY C1.Class;