使用MAX选择连接值的行

Question

请考虑以下架构。

学生：

StudentID uniqueidentifier
Name varchar(max)
FKTeacherID uniqueidentifier

TestScore：

TestScoreID uniqueidentifier
Score int
FKStudentID uniqueidentifier

我的目标是编写一个查询，生成每个老师的最高考试分数和达到最高分数的学生。返回教师的id (Student.FKTeacherID)、取得的成绩(TestScore.Score)和取得成绩的学生(Student.Name)。

我可以编写类似这样的代码来获得前两个必需的列：

SELECT FKTeacherID, MAX(Score) MaxScore
FROM Student
JOIN TestScore on FKStudentID = StudentID
GROUP BY FKTeacherID

但是，如果不将其添加到group by子句中，我将无法获得相关的Student.Name，这将更改结果集。

Answer 1

如果我理解正确的话，一种选择是使用row_number() --下面是一个带有公共表表达式的示例：

with cte as (
    select s.fkteacherid,
        ts.score,
        s.name,
        row_number() over (partition by s.fkteacherid order by ts.score desc) rn
    from student s
        inner join testscore ts on s.studentid = ts.fkstudentid
)
select fkteacherid, score, name
from cte
where rn = 1

基本思想是按fkteacherid分组，在每个组中按score desc排序，然后从每个组中获取第一条记录。

Answer 2

更新

如果可能，请尝试更新后的查询：

SELECT 
    FKTeacherID, Name, Score
FROM 
    Student
    JOIN TestScore on FKStudentID = StudentID
    JOIN
    (
        SELECT 
            B.FKTeacherID AS TeacherID, MAX(A.Score) MaxScore 
        FROM 
            Student B
            JOIN TestScore A on A.FKStudentID = B.StudentID
        GROUP BY    
            B.FKTeacherID
    ) As TeachersMaxScore
    ON TeachersMaxScore.TeacherID = FKTeacherID 
       AND TeachersMaxScore.MaxScore = Score