在SQL中查找值最大的记录

I have the following table:

我有以下表格:

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

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

如何在每个“类”中找到最大“分数”的“名称”?

Required Output:

要求输出:

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

This is for MySQL.

这是MySQL。

3 个解决方案

#1

WITH ClassScores AS
(
    SELECT 1 AS class, 'Anna' AS name, 34 AS score 
    UNION
    SELECT 1, 'Andy', 80  
    UNION
    SELECT 2, '*e', 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;

#2

Another way - if the ClassScores has a (hidden) PRIMARY KEY:

另一种方法——如果课堂分数有一个(隐藏的)主键:

SELECT 
    cs.Class
  , cs.Name
  , cs.Score
FROM 
      ( SELECT DISTINCT Class 
        FROM ClassScores 
      ) AS csd
  JOIN 
      ClassScores AS cs
    ON cs.PK = 
       ( SELECT csm.PK
         FROM ClassScores csm
         WHERE csm.Class = csd.Class
         ORDER BY csm.Score DESC
         LIMIT 1
       )

#3

Use UNION and then you can use three select statements individually. It will clean up the code nicely.

使用UNION，然后可以单独使用三个select语句。它可以很好地清理代码。

Try..

试一试. .

select class, name, max(score) as "Score" from yourTable where class=1
UNION
select class, name, max(score) as "Score" from yourTable where class=2
UNION
select class,name,max(score) as "Score" from yourTable where class=3

#1

WITH ClassScores AS
(
    SELECT 1 AS class, 'Anna' AS name, 34 AS score 
    UNION
    SELECT 1, 'Andy', 80  
    UNION
    SELECT 2, '*e', 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;

#2