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

阅读 162

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