如何正确实现聚合函数？获取指定课程最高分及对应学生信息

一、获取指定课程（如Math）的最高分及学生姓名 #

在主查询和子查询中同时添加课程名称过滤条件即可：

SELECT s.Name, l.Grade
FROM Students s
INNER JOIN Lessons l ON s.Id = l.StudentId
WHERE l.Name = 'Math' 
AND l.Grade = (
    SELECT MAX(Grade) 
    FROM Lessons 
    WHERE Name = 'Math'
)

• 双条件过滤确保只针对目标课程计算最高分并匹配对应学生
• 若该课程有多名学生同分最高分，会返回所有符合条件的记录

二、获取所有课程的最高分及对应学生 #

方法1：使用窗口函数（推荐） #

通过RANK()按课程分组并排序，保留同分的排名：

WITH RankedLessons AS (
    SELECT 
        l.Name AS LessonName,
        s.Name AS StudentName,
        l.Grade,
        RANK() OVER(PARTITION BY l.Name ORDER BY l.Grade DESC) AS GradeRank
    FROM Lessons l
    INNER JOIN Students s ON l.StudentId = s.Id
)
SELECT LessonName, StudentName, Grade
FROM RankedLessons
WHERE GradeRank = 1

• 若只需返回单条记录（无论是否同分），可替换RANK()为ROW_NUMBER()

方法2：使用关联子查询 #

针对每条课程记录，匹配对应课程的最高分：

SELECT 
    l.Name AS LessonName,
    s.Name AS StudentName,
    l.Grade
FROM Lessons l
INNER JOIN Students s ON l.StudentId = s.Id
WHERE l.Grade = (
    SELECT MAX(Grade) 
    FROM Lessons 
    WHERE Name = l.Name
)

三、EF Core 对应实现（适配你的实体类） #

获取指定课程（Math）的记录 #

var mathTopStudents = context.Lessons
    .Where(l => l.Name == "Math")
    .GroupBy(l => l.Name)
    .Select(g => new {
        LessonName = g.Key,
        MaxGrade = g.Max(l => l.Grade)
    })
    .Join(context.Lessons.Include(l => l.Student),
          max => new { max.LessonName, max.MaxGrade },
          l => new { l.Name, l.Grade },
          (max, l) => new {
              LessonName = max.LessonName,
              StudentName = l.Student.Name,
              max.MaxGrade
          })
    .Distinct()
    .ToList();

获取所有课程的最高分记录 #

var allTopStudents = context.Lessons
    .Include(l => l.Student)
    .Select(l => new {
        l.Name,
        l.Grade,
        StudentName = l.Student.Name,
        MaxGradeForLesson = context.Lessons.Where(ll => ll.Name == l.Name).Max(ll => ll.Grade)
    })
    .Where(x => x.Grade == x.MaxGradeForLesson)
    .Select(x => new {
        LessonName = x.Name,
        x.StudentName,
        x.Grade
    })
    .Distinct()
    .ToList();

内容的提问来源于stack exchange，提问作者amin khosravi