sql - 使用聚合选择列时出现问题 (SQL Server)

Question

我真的卡住了。请耐心等待，因为我是数据库的新手。:)

无论如何，我需要显示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' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

但我不能在 GROUP BY 中包含 Subject_Code，因为结果会有所不同。

我该怎么做才能只显示

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

我该如何解决这个错误？

Answer 1

看来您正在寻找一个群体明智的最大值。下面是一种连接回包含每个学生最高成绩的派生表的方法(这种方法适用于大多数 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 .此外，还有一个使用 ROW_NUMBER 的示例，当学生在两个或多个科目中的分数相同时，可以使用额外的任意 ORDER BY 来选择一个顶级科目。