mysql - 类(class)明智，具有最大总分的学生姓名

Question

假设我有一个表如下:

Class   |   Subject | Student   | Marks
----------------------------------------
1       |   Maths   |   A       |   70   
1       |   Eng     |   B       |   80
1       |   IT      |   A       |   90 
1       |   IT      |   C       |   80 
2       |   Maths   |   D       |   60   
2       |   Eng     |   E       |   75
2       |   Maths   |   E       |   90 
2       |   IT      |   F       |   80 
3       |   Maths   |   A       |   160   
3       |   Eng     |   B       |   165
3       |   IT      |   G       |   90 

我想要输出为

Class   |   Student     | Marks
----------------------------------------
1       |       A       |   160   
2       |       E       |   165
3       |       B       |   165 

即结果包含具有最大总分的类(class)学生姓名。如何为此编写 SQL 查询？例如对于类(class) 1，学生 A 有 70 + 90 = 160，这比 B 和 C 都达到 80 成为最大值。

Answer 1

一种解决方案是计算每个学生在每个类(class)的最高分，并将其用作过滤连接:

select  ClassStudentSum.*
from    (
        select  class
        ,       student
        ,       sum(Marks) as SumMarks
        from    YourTable
        group by
                class
        ,       student
        ) as ClassStudentSum
join    (
        select  class
        ,       max(SumMarks) as MaxSumMarks
        from    (
                select  class
                ,       student
                ,       sum(Marks) as SumMarks
                from    YourTable
                group by
                        class
                ,       student
                ) ClassStudentSum2
        group by
                class
        ) MaxPerClass
on      MaxPerClass.class = ClassStudentSum.class
        and MaxPerClass.MaxSumMarks = ClassStudentSum.SumMarks

Live example at SQL Fiddle.