在SQL中查找具有最大值的记录 [英] Finding the record with maximum value in SQL

问题描述

我有下表:

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

如何在每个班级"中找到最大得分"的名称"?

How to I find the 'Name' with maximum 'Score' in each 'Class' ?

必需的输出:

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

这是针对MySQL的.

This is for MySQL.

推荐答案

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;

这篇关于在SQL中查找具有最大值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！