用窗口函数：

SELECT course, student_id, score
FROM (
    SELECT *, RANK() OVER (PARTITION BY course ORDER BY score DESC) AS rank
    FROM Scores
) t
WHERE rank = 1;
复制

oracle,mysql通用语法:

SELECT b.course, b.score, a.student_id
FROM Scores a INNER JOIN (SELECT course，MAX(score) AS maxscore FROM Scores GROUP BY course) b ON a.course=b.course AND a.score=b.maxscore;

select * from (select student_id,course,score,rank () over (partition by score) as ranking from scores) where ranking=1;

SELECT s.course, s.student_id, s.score
FROM Scores s
INNER JOIN (
SELECT course, MAX(score) AS max_score
FROM Scores
GROUP BY course
) AS max_scores
ON s.course = max_scores.course AND s.score = max_scores.max_score;