mysql - MySQL 可以返回 "max"行中的列吗？

Question

我的表格中有以下数据:-

Student     Subject     Marks
Peter       English     85
Peter       Maths       79
Peter       Science     80
Milind      English     71
Milind      Maths       95
Milind      Science     89

我想编写一个查询，为每个学生提供他/她得分最高的科目。在这种情况下，它将是

Peter   English
Milind  Maths

预先感谢您的帮助。米林德。

Answer 1

你可以像下面这样做:

SELECT 
  t1.Student, t1.Subject
FROM your_table t1
INNER JOIN (
  SELECT MAX(Marks) AS max_marks, Subject 
  FROM your_table
  GROUP BY Subject
) t2 ON t1.Subject = t2.Subject AND t1.Marks = t2.max_marks