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

小编典典

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

sql

我有下表：

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的。

阅读 194

2021-03-08

共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;

2021-03-08