[Programmers / MySQL] 물고기 종류 별 대어 찾기 (Level 3)

📌 문제

문제 설명

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. FISH_INFO 테이블의 구조는 다과 같으며 ID, FISH_TYPE, LENGTH, TIME은 각각 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다.

 

단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

FISH_NAME_INFO 테이블은 물고기의 이름에 대한 정보를 담고 있습니다. FISH_NAME_INFO 테이블의 구조는 다음과 같으며, FISH_TYPE, FISH_NAME 은 각각 물고기의 종류(숫자), 물고기의 이름(문자) 입니다.

 

문제

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.
물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.
결과는 물고기의 ID에 대해 오름차순 정렬해주세요.
단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

예시

예를 들어 FISH_INFO 테이블이 다음과 같고

 

FISH_NAME_INFO 테이블이 다음과 같다면

'BASS' 중 가장 큰 물고기는 60cm 로 물고기 ID 가 6이고, 'SNAPPER' 중 가장 큰 물고기는 73cm 로 물고기 ID가 9입니다. 'ANCHOVY' 중 가장 큰 물고기는 73cm 로 물고기 ID가 8입니다. 따라서 물고기 ID(ID) 에 대해 오름차순 정렬한다면 결는 다음과 같습니다.

---

📌 나의 풀이

Code

SELECT ID, FISH_NAME, LENGTH
FROM FISH_INFO I
JOIN FISH_NAME_INFO N
ON I.FISH_TYPE = N.FISH_TYPE
WHERE (I.FISH_TYPE, LENGTH) IN (SELECT FISH_TYPE, MAX(LENGTH)
                                FROM FISH_INFO
                                GROUP BY FISH_TYPE)
ORDER BY ID;

Solution

1. FISH_INFO 테이블과 FISH_NAME_INFO 테이블을 FISH_TYPE 컬럼을 키로 하여 INNER JOIN

• FISH_NAME_INFO 테이블의 FISH_NAME 컬럼을 가져오기 위함

2. WHERE 문의 IN 뒤에 사용할 서브쿼리 작성

• FISH_INFO 테이블에서 FISH_TYPE 컬럼을 기준으로 그룹화
• 그룹화된 상태에서 FISH_INFO와 각 FISH_INFO 그룹에서 가장 LENGTH가 긴 행만 가져오기

3. 메인 쿼리의 FISH_TYPE과 LENGTH가 동시에 서브쿼리 테이블 안에 포함되는 행만 필터링

• 두 컬럼을 튜플로 묶은 뒤, IN 사용

4. ID를 기준으로 오름차순 정렬

 

5. ID,  FISH_NAME, LENGTH 컬럼만 출력

---

📌 다른 사람의 풀이

Code

SELECT A.ID, FISH_NAME, A.LENGTH 
FROM FISH_INFO A 
JOIN FISH_NAME_INFO B 
ON A.FISH_TYPE = B.FISH_TYPE
WHERE A.LENGTH = (
    SELECT MAX(LENGTH)
    FROM FISH_INFO
    WHERE FISH_TYPE = A.FISH_TYPE
)
ORDER BY ID;

Solution

1. FISH_INFO 테이블과 FISH_NAME_INFO 테이블을 FISH_TYPE 컬럼을 키로 하여 INNER JOIN

• FISH_NAME_INFO 테이블의 FISH_NAME 컬럼을 가져오기 위함

2. 메인 쿼리에서의 LENGTH 값을 필터링해 줄 서브 쿼리 작성

• WHERE FISH_TYPE = A.FISH_TYPE을 통해 마치 FISH_TYPE에 대해 그룹화한 것과 같은 효과를 가져옴
• 메인 쿼리에 존재하는 각 FISH_TYPE 그룹에 대해 가장 긴 LENGTH만 가져오기

3. FISH_TYPE 그룹 별 가장 긴 LENGTH에 해당하는 LENGTH를 가진 행만 필터링

• 메인 쿼리의 LENGTH와 서브쿼리를 '='로 비교

4. ID를 기준으로 오름차순 정렬

 

5. ID,  FISH_NAME, LENGTH 컬럼만 출력

---

배운 내용

1. IN을 사용할 때, 두 컬럼을 튜플로 묶어 함께 포함되는 행을 필터링할 수 있다.

 

2. 메인 쿼리의 WHERE 절에서 서브 쿼리를 사용하면, 서브쿼리의 WHERE절이 GROUP BY 처럼 동작하도록 만들 수 있다.

WHERE A.LENGTH = (
    SELECT MAX(LENGTH)
    FROM FISH_INFO
    WHERE FISH_TYPE = A.FISH_TYPE
)

• WHERE FISH_TYPE = A.FISH_TYPE는 서브쿼리 안에서 메인 쿼리의 FISH_TYPE 값과 동일한 값을 가지는 물고기 그룹을 선택합니다.
• 이를 통해 각 FISH_TYPE에 대한 최대 길이를 구하고, 그 값과 메인 쿼리에서의 LENGTH 값을 비교하게 됩니다.