[프로그래머스 SQL Lv.3] 물고기 종류 별 대어 찾기 (MySQL)

문제링크 : https://school.programmers.co.kr/learn/courses/30/lessons/293261

프로그래머스

 

1. 테이블

(1) FISH_INFO

Column name	Type	Nullable
ID	INTEGER	FALSE
FISH_TYPE	INTEGER	FALSE
LENGTH	FLOAT	TRUE
TIME	DATE	FALSE

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

(2) FISH_NAME_INFO

Column name	Type	Nullable	Description
FISH_TYPE	INTEGER	FALSE	물고기의 종류(숫자)
FISH_NAME	VARCHAR	FALSE	물고기의 이름(문자)

 

2. 문제

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

 

3. 문제풀이

[ 물고기 종류 별로 가장 큰 물고기 구하기 ]

처음에는 메인 쿼리에서 GROUP BY를 사용해 해결하려고 했다. 하지만 결론적으로 이 방식은 사용할 수 없었다.

메인 쿼리의 SELECT 절에는 반드시 id와 fish_name이 포함되어야 했는데,
이 상태에서 GROUP BY를 사용하면 id와 fish_name 역시 GROUP BY 절에 모두 포함되어야 하기 때문이다.

[GROUP BY 원칙]
SELECT 절에 집계 함수와 함께 사용되지 않은 컬럼은
반드시 GROUP BY 절에 포함되어야 한다.

 

결과적으로 이렇게 되면 원하는 기준으로의 집계가 불가능해졌고, 메인 쿼리에서 GROUP BY를 사용하는 접근은 포기할 수밖에 없었다.

그래서 WHERE 절에 서브쿼리를 사용하는 방식으로 방향을 변경했다. 즉, 중첩 서브쿼리를 활용해 먼저 필요한 조건을 만족하는 데이터만 추린 뒤, 다중 컬럼에 대해 IN 절을 사용해 메인 쿼리를 필터링하는 방식으로 해결했다.

먼저 서브쿼리만 보자면 다음과 같다.

SELECT fish_type, MAX(length)  -- 물고기 종류와, 종류별 가장 큰 물고기 조회
FROM fish_info
GROUP BY fish_type   -- 물고기 종류별 그룹화

위의 서브쿼리를 메인 서브쿼리의 WHERE절에 사용한다. 이때 다중컬럼 in을 사용해서 물고기 종류와 길이가 서브쿼리 결과와 같은 것만 필터링한다. 즉 물고기 종류별로 길이가 가장 큰 물고기만 필터링된다.

WHERE (fish_type, length) in (  -- 다중컬럼 in 사용해서 종류와 길이가 서브쿼리 결과와 같은 것만 필터링
    SELECT fish_type, MAX(length)
    FROM fish_info
    GROUP BY fish_type
)

[ 물고기의 ID, 이름, 길이 출력하기 ]

물고기의 id와 길이는 FISH_INFO 테이블에, 이름은 FISH_NAME_INFO 테이블에 있으므로 두 테이블을 JOIN하고 필요한 컬럼만 조회한다.

이때 SELECT문에 T1.id 처럼 특정 테이블을 지정하고 컬럼명을 적어도 결과에는 id 처럼 테이블명 없이 컬럼명만 출력된다.

SELECT T1.id, T2.fish_name, T1.length
FROM fish_info T1
JOIN fish_name_info T2
ON T1.fish_type = T2.fish_type
WHERE (T1.fish_type, T1.length) in (  -- 각 컬럼이 어떤 테이블에서 온건지 명시해준다.
    SELECT fish_type, MAX(length)
    FROM fish_info
    GROUP BY fish_type
)

[ 결과를 물고기 ID에 대해 오름차순 정렬하기 ]

SELECT T1.id, T2.fish_name, T1.length
FROM fish_info T1
JOIN fish_name_info T2
ON T1.fish_type = T2.fish_type
WHERE (T1.fish_type, T1.length) in (  
    SELECT fish_type, MAX(length)
    FROM fish_info
    GROUP BY fish_type
)
ORDER BY T1.id  -- id 기준 오름차순 정렬

 

4. 정답

따라서 정답은

SELECT T1.id, T2.fish_name, T1.length
FROM fish_info T1
JOIN fish_name_info T2
ON T1.fish_type = T2.fish_type
WHERE (T1.fish_type, T1.length) in ( 
    SELECT fish_type, MAX(length)
    FROM fish_info
    GROUP BY fish_type
)
ORDER BY T1.id

 

 

---

Reference

https://dololak.tistory.com/812