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

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

제가 말씀드리고 있는 내용을 간략하게 보여드리겠습니다.

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를 이용한 답변을 환영합니다.

EDITED 질문에 답변(즉, 연관된 열도 가져오기).

Sql 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-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;

편집: 마크가 댓글로 잘 잡아준 덕분에 적절한 중복 제거 쿼리 추가

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.name

이거 먹어봐요.

Select student.name, max(result.score) As Score from Student
        INNER JOIN
    result
        ON student.ID = result.student_id
GROUP BY
    student.name

Oracle의 분석 기능을 사용하면 다음과 같은 작업이 쉬워집니다.

SELECT DISTINCT
       students.name
      ,FIRST_VALUE(exam_results.score)
       OVER (PARTITION BY students.id
             ORDER BY exam_results.score DESC) AS score
      ,FIRST_VALUE(exam_results.date)
       OVER (PARTITION BY students.id
             ORDER BY exam_results.score DESC) AS date
FROM   students, exam_results
WHERE  students.id = exam_results.student_id;

Select Name, T.Score, er. date 
from Students S inner join
          (Select Student_ID,Max(Score) as Score from Exam_Results
           Group by Student_ID) T 
On S.id=T.Student_ID inner join Exam_Result er
On er.Student_ID = T.Student_ID And er.Score=T.Score

MS SQL Server 사용:

SELECT name, score, date FROM exam_results
JOIN students ON student_id = students.id
JOIN (SELECT DISTINCT student_id FROM exam_results) T1
ON exam_results.student_id = T1.student_id
WHERE exam_results.id = (
    SELECT TOP(1) id FROM exam_results T2
    WHERE exam_results.student_id = T2.student_id
    ORDER BY score DESC, date ASC)

동점일 경우 가장 오래된 날짜가 반환됩니다(변경).date ASC로.date DESC가장 최근의 것을 반환합니다.)

출력:

Jim 93  2009-01-03 00:00:00.000
Joe 27  2009-04-09 00:00:00.000
Jay 100 2009-01-06 00:00:00.000

테스트 베드:

CREATE TABLE students(id int , name nvarchar(20) );

CREATE TABLE exam_results(id int , student_id int , score int, date datetime);

INSERT INTO students
VALUES
(1,'Jim'),(2,'Joe'),(3,'Jay')

INSERT INTO exam_results VALUES
(1, 1, 73, '8/1/09'), 
(2, 1, 93, '9/2/09'),
(3, 1, 93, '1/3/09'),
(4, 2, 27, '4/9/09'),
(5, 2, 17, '8/9/09'),
(6, 3, 100, '1/6/09')

SELECT name, score, date FROM exam_results
JOIN students ON student_id = students.id
JOIN (SELECT DISTINCT student_id FROM exam_results) T1
ON exam_results.student_id = T1.student_id
WHERE exam_results.id = (
    SELECT TOP(1) id FROM exam_results T2
    WHERE exam_results.student_id = T2.student_id
    ORDER BY score DESC, date ASC)

MySQL에서는 문장 끝에 TOP(1)을 LIMIT 1로 변경할 수 있다고 생각합니다.저는 이것을 테스트해 본 적이 없습니다.

언급URL : https://stackoverflow.com/questions/1863606/joining-tables-based-on-the-maximum-value