获取最多3列总和的行-MySQL

Question

我被一些查询逻辑困住了。我有一个名为＜code＞student＜/code＞的表。表上有每个学期学生的分数。有n学期数。我需要得到每学期最高分数（mark1 mark2 mark3）的学生的姓名和id。

这是我的桌子结构

|id| name | mark1| mark2| mark3| sem|
|1 | Harry| 8    | 9    | 9    | 1  |
|2 | John | 10   | 8    | 10   | 1  |
|3 | Derek| 4    | 5    | 8    | 1  |
|4 | Dona | 8    | 9    | 5    | 1  |
|5 | Ammy | 9    | 9    | 9    | 2  |
|6 | Kate | 10   | 7    | 10   | 2  |
|7 | Aby  | 3    | 5    | 4    | 2  |
|8 | Eliza| 5    | 9    | 5    | 2  |

所需输出

|id| name | mark1| mark2| mark3| sem| maxmark|
|2 | John | 10   | 8    | 10   | 1  |   28   |
|5 | Ammy | 9    | 9    | 9    | 2  |   27   |
|6 | Kate | 10   | 7    | 10   | 2  |   27   |

我想让Maxmark

SELECT *, (MAX(`mark1` + `mark2` + `mark3`)) AS maxmark
FROM `stud`
GROUP BY `studid`

Answer 1

您必须按sem进行分组，以获得子查询中每个sem的最大标记，一旦每个sem有了最大标记，就可以在这样的子查询中使用in，

SELECT *, mark1+mark2+mark3 AS maxmark 
from student
where (sem, mark1 + mark2 + mark3) in (
SELECT sem, MAX(mark1 + mark2 + mark3) AS maxmark
FROM student
GROUP BY sem ) 

输出

|id| name | mark1| mark2| mark3| sem| maxmark|
|2 | John | 10   | 8    | 10   | 1  |   28   |
|5 | Ammy | 9    | 9    | 9    | 2  |   27   |
|6 | Kate | 10   | 7    | 10   | 2  |   27   |

你可以从互联网上了解更多，https://www.techonthenet.com/mysql/in.php祝你好运，希望这有帮助。

Answer 2

这个查询会给你你想要的结果。它将student表连接到一个填充了每学期最高分的表，只显示该学期达到最高分的学生(S2 . max mark = S1 . mark 1 S1 . mark 2 S1 . mark 3)。

SELECT s1.*, s2.maxmark
FROM student s1
JOIN (SELECT sem, MAX(mark1 + mark2 + mark3) AS maxmark
      FROM student
      GROUP BY sem) s2
ON s2.sem = s1.sem AND s2.maxmark = s1.mark1+s1.mark2+s1.mark3

输出：

id  name    mark1   mark2   mark3   sem     maxmark
2   John    10      8       10      1       28
5   Ammy    9       9       9       2       27
6   Kate    10      7       10      2       27

SQL小提琴演示