每门课程的最高分数及学生姓名

Question

我想在每门课程中获得最高分，包括该课程的标题和参加该课程考试的学生姓名。我有这些表格，第一个学生

课程表

我还做了一张表格，让学生在课程考试中通过分数与课程联系起来

那我怎么能质疑呢。

Answer 1

我使用带有课程id分区和考试分数desc的RANK（）来获得排名。然后选择排名=1以获得排名的最高值。Windows函数对于这些类型的Qestion非常有用。

SELECT 
  A.[CourseId]
 ,A.[exam_mark]
 ,A.[name]
 ,A.[title]
FROM
(
     SELECT
      RANK() OVER (PARTITION BY [mark].[course_id]   ORDER BY  [mark].[exam_mark] DESC ) AS [rnk] 
     ,[Courses].[id] AS [CourseId]
     ,[Courses].[title] 
     ,[mark].[exam_mark]
     ,[students_].[name]
   FROM mark
       INNER JOIN  students_ 
             ON  mark.student_id = students_.id
       INNER JOIN  Courses 
             ON  mark.course_id = Courses.id

  ) AS A 
   WHERE A.rnk = 1

Answer 2

您可以使用窗口函数。一种方法是：

select . . .   -- whatever columns you want
from (select sc.*,
             rank() over (partition by course_id order by exam_mark desc) as seqnum
      from student_courses sc
     ) sc join
     students s
     on sc.student_id = s.id join
     courses c
     on sc.course_id = c.id
where seqnum = 1;