[3] WITH 문 연습

MYSQL의 WITH문은 하나 이상의 서브쿼리에서 반환된 데이터를 단일쿼리에서 재사용하게 해 주고 임시 테이블에 저장시켜 주는 문법입니다. 무엇보다 WITH문의 장점은 쿼리를 단순화하고 가독성을 높일 수 있습니다.

 

1. 물고기 종류별 대어찾기

https://school.programmers.co.kr/learn/courses/30/lessons/293261

프로그래머스

물고기 종류 별로 길이가 가장 큰 물고기의 ID, 이름, 길이를 출력하는 쿼리문을 작성

주의사항) 출력의 칼럼명은 각각 ID, FISH_NAME, LENGTH / ID에 대해 오름차순 정렬 / 물고기 종류별 가장 큰 물고기는 1마리만 있고, 10CM 이하의 물고기가 가장 큰 경우는 없다

• FISH_INFO 테이블에서 종류별 가장 긴 물고기를 찾는 WITH 문 작성
  • ROW_NUMBER로 각 그룹의 순번 매기기 -> 물고기 종류별로 길이가 가장 긴 물고기에게 1 지정
• FISH_NAME_INFO 테이블과 FISH_TYPE을 기준으로 INNER JOIN

WITH temp1 AS (
    SELECT ID, FISH_TYPE, LENGTH, ROW_NUMBER() OVER (
            PARTITION BY FISH_TYPE ORDER BY LENGTH DESC) AS le
    FROM FISH_INFO WHERE LENGTH IS NOT NULL
)
SELECT a.ID, b.FISH_NAME, a.LENGTH FROM temp1 AS a 
INNER JOIN FISH_NAME_INFO AS b ON a.FISH_TYPE=b.FISH_TYPE
WHERE a.le=1
ORDER BY a.ID ASC;

 

2. 즐겨찾기가 가장 많은 식당 정보 출력

https://school.programmers.co.kr/learn/courses/30/lessons/131123

프로그래머스

음식 종류별로 즐겨찾기 수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기 수를 조회하는 쿼리문 작성

주의사항) 음식 종류를 기준으로 내림차순 정렬한 결과를 산출

• 테이블에서 즐겨찾기 수(FAVORITES)가 가장 많은 식당 찾기
  • 음식종류(FOOD_TYPE)를 PARTITION 기준으로 설정하고 즐겨찾기 수(FAVORITES)가 최대인 식당을 찾기
• WITH 문 내 결과에 부합한 조건을 테이블에서 찾기

WITH temp1 AS (
    SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES,
    MAX(FAVORITES) OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS a
    FROM REST_INFO
)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES FROM temp1
WHERE FAVORITES=a
ORDER BY FOOD_TYPE DESC;