基于最大值的连接表
基于最大值的连接表
提问于 2009-12-07 23:22:49
下面是我所说的一个简单的例子：
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 |
|____|____________|_______|________|假设，为了这个问题，每个学生至少有一个考试成绩记录。
你会如何选择每个学生以及他们的最高分？编辑：...AND记录中的其他字段？
预期产出：
_________________________
| name | score | date |
|------+-------|--------|
| Jim | 93 | 1/3/09 |
| Joe | 27 | 4/9/09 |
| Jay | 100 | 1/6/09 |
|______|_______|________|欢迎使用各种数据库管理系统的答案。
回答 6
Stack Overflow用户
回答已采纳
发布于 2009-12-07 23:44:12
回答编辑的问题(也就是获得相关的列)。
在Server 2005+中，最好的方法是将排序/窗口函数与CTE结合使用，如下所示：
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的解决方案，子查询和自连接将工作，如下所示：
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;最后一种假设没有重复的学生_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;编辑:添加了适当的反复制查询，这要归功于Mark在评论中的出色表现
Stack Overflow用户
发布于 2009-12-07 23:25:33
SELECT s.name,
COALESCE(MAX(er.score), 0) AS high_score
FROM STUDENTS s
LEFT JOIN EXAM_RESULTS er ON er.student_id = s.id
GROUP BY s.nameStack Overflow用户
发布于 2009-12-07 23:32:46
尝尝这个,
Select student.name, max(result.score) As Score from Student
INNER JOIN
result
ON student.ID = result.student_id
GROUP BY
student.name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1863606
复制相关文章
相似问题
腾讯云开发者
