根据最大值联接表 [英] Joining tables based on the maximum value
问题描述
这是我在说什么的简化示例:
Here's a simplified example of what I'm talking about:
Table: students exam_results
_____________ ____________________________________
| id | name | | id | student_id | score | date |
|----+------| |----+------------+-------+--------|
| 1 | Jim | | 1 | 1 | 73 | 8/1/09 |
| 2 | Joe | | 2 | 1 | 67 | 9/2/09 |
| 3 | Jay | | 3 | 1 | 93 | 1/3/09 |
|____|______| | 4 | 2 | 27 | 4/9/09 |
| 5 | 2 | 17 | 8/9/09 |
| 6 | 3 | 100 | 1/6/09 |
|____|____________|_______|________|
出于这个问题,假设每个学生至少记录了一个考试结果.
Assume, for the sake of this question, that every student has at least one exam result recorded.
您将如何选择每个学生以及他们的最高分数? 编辑:...以及该记录中的其他字段?
How would you select each student along with their highest score? Edit: ...AND the other fields in that record?
预期输出:
_________________________
| name | score | date |
|------+-------|--------|
| Jim | 93 | 1/3/09 |
| Joe | 27 | 4/9/09 |
| Jay | 100 | 1/6/09 |
|______|_______|________|
欢迎使用所有类型的DBMS的答案.
Answers using all types of DBMS are welcome.
推荐答案
回答已编辑的问题(即也获取关联的列).
Answering the EDITED question (i.e. to get associated columns as well).
在Sql Server 2005+中,最好的方法是使用排名/窗口功能与 CTE 结合使用,如下所示:
In Sql Server 2005+, the best approach would be to use a ranking/window function in conjunction with a CTE, like this:
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的解决方案,子查询和自联接将可以正常工作,如下所示:
For an ANSI-SQL compliant solution, a subquery and self-join will work, like this:
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打破平局,则可以执行以下操作:
This last one assumes there aren't duplicate student_id/max_score combinations, if there are and/or you want to plan to de-duplicate them, you'll need to use another subquery to join to with something deterministic to decide which record to pull. For example, assuming you can't have multiple records for a given student with the same date, if you wanted to break a tie based on the most recent max_score, you'd do something like the following:
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;
由于Mark的注释很好,因此添加了适当的重复数据删除查询
Added proper de-duplicating query thanks to Mark's good catch in comments
这篇关于根据最大值联接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!