MySQL 최대 값을 기준으로 테이블 결합

다음은 내가 말하는 것에 대한 간단한 예입니다.

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 |
                    |____|____________|_______|________|

이 질문을 위해 모든 학생이 적어도 하나의 시험 결과를 기록했다고 가정합니다.

최고 점수와 함께 각 학생을 어떻게 선택 하시겠습니까? 수정 : ... 그리고 해당 기록의 다른 필드는?

예상 출력 :

_________________________
| name | score |   date |
|------+-------|--------|
|  Jim |    93 | 1/3/09 |
|  Joe |    27 | 4/9/09 |
|  Jay |   100 | 1/6/09 |
|______|_______|________|

모든 유형의 DBMS를 사용한 답변을 환영합니다.

 

해결 방법

 

편집 된 질문에 답하기 (즉, 연관된 열도 가져 오기 위해).

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;

편집 : 주석에서 Mark의 좋은 캐치 덕분에 적절한 중복 제거 쿼리 추가

 

참조 페이지 https://stackoverflow.com/questions/1863606