回答编辑的问题(即也获取关联的列)。
在 Sql Server 2005+ 中,最好的方法是使用排序/窗口函数 http://msdn.microsoft.com/en-us/library/ms189798.aspx与CTE http://msdn.microsoft.com/en-us/library/ms190766.aspx, 像这样:
with exam_data as
(
select r.student_id, r.score, r.date,
row_number() over(partition by r.student_id order by r.score desc) as rn
from exam_results r
)
select s.name, d.score, d.date, d.student_id
from students s
join exam_data d
on s.id = d.student_id
where d.rn = 1;
对于符合 ANSI-SQL 的解决方案,子查询和自连接将起作用,如下所示:
select s.name, r.student_id, r.score, r.date
from (
select r.student_id, max(r.score) as max_score
from exam_results r
group by r.student_id
) d
join exam_results r
on r.student_id = d.student_id
and r.score = d.max_score
join students s
on s.id = r.student_id;
最后一个假设不存在重复的 Student_id/max_score 组合,如果存在和/或您想要计划删除它们,则需要使用另一个子查询来加入确定性的内容来决定提取哪条记录。例如,假设您不能拥有同一日期的给定学生的多个记录,如果您想根据最近的 max_score 打破平局,您可以执行如下操作:
select s.name, r3.student_id, r3.score, r3.date, r3.other_column_a, ...
from (
select r2.student_id, r2.score as max_score, max(r2.date) as max_score_max_date
from (
select r1.student_id, max(r1.score) as max_score
from exam_results r1
group by r1.student_id
) d
join exam_results r2
on r2.student_id = d.student_id
and r2.score = d.max_score
group by r2.student_id, r2.score
) r
join exam_results r3
on r3.student_id = r.student_id
and r3.score = r.max_score
and r3.date = r.max_score_max_date
join students s
on s.id = r3.student_id;
编辑:由于马克在评论中的出色表现,添加了适当的去重复查询