sql - 选择带有聚合的列时出现问题(SQL Server)

我被卡住了。不过，请容忍我，因为我对数据库还不熟悉。
无论如何，我需要显示StudentID，学生的最高年级所在的科目，以及该科目的年级。
这是我的密码：

SELECT
    Grades.Student_ID,
    Subject.Subject_Code,
    MAX(Grades.Grade)
FROM
    Grades
LEFT JOIN
    Subject ON Grades.Subject_ID = Subject.Subject_ID
GROUP BY
    Grades.Student_ID

但它有一个错误：
“subject.subject_code”在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。
但我不能把Subject_Code包含在GROUP BY中，因为结果会有所不同。
我能做些什么呢？

Student_ID || (subject with highest grade) || (grade of that subject)

我怎样才能克服这个错误？

最佳答案

看来你正在寻找一个组明智的最大值。下面是一种方法，它将返回到包含每个学生最高成绩的派生表（这种方法应该适用于大多数RDBMS，包括MySQL）：

SELECT X.Student_ID,
    s.Subject_Code,
    x.TopGrade
FROM
(
  SELECT
    Grades.Student_ID,
    MAX(Grades.Grade) AS TopGrade
  FROM Grades
  GROUP BY Grades.Student_ID
) x
INNER JOIN Grades g
ON g.Student_ID = x.Student_ID AND g.Grade = x.TopGrade
LEFT JOIN Subject s
      ON g.Subject_ID = s.Subject_ID

如果同一个学生有两个或多个分数完全相同，它将返回所有科目。
这是我最初的答案，它将在sql server上运行

SELECT x.Student_ID, x.Subject_Code, x.Grade
FROM
(
  SELECT
    Grades.Student_ID,
    Subject.Subject_Code,
    RANK() OVER (PARTITION BY Grades.Student_ID ORDER BY Grades.Grade DESC) AS [Rank],
    Grades.Grade
  FROM Grades
  LEFT JOIN Subject
      ON Grades.Subject_ID = Subject.Subject_ID
) x
WHERE x.[Rank] = 1;

SqlFiddle of both the above queries here。此外，还有一个例子，当学生在两个或多个科目中的分数相等时，可以使用带有额外任意ORDER BY的行数来选择一个热门科目。