SQL/GROUP BY
[프로그래머스 ORACLE SQL]식품분류별 가장 비싼 식품의 정보 조회하기★SELF JOIN★VER2.0
goAhEAd_29
2023. 6. 22. 01:21
728x90
반응형
https://school.programmers.co.kr/learn/courses/30/lessons/131116
VERSION 2.0
SELECT A.CATEGORY , A.MAX_PRICE AS MAX_PRICE , B.PRODUCT_NAME FROM
(SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE FROM FOOD_PRODUCT
GROUP BY CATEGORY
HAVING CATEGORY IN ( '과자', '국', '김치', '식용유')) A LEFT JOIN
FOOD_PRODUCT B ON A.CATEGORY = B.CATEGORY AND A.MAX_PRICE = B.PRICE
ORDER BY MAX_PRICE DESC
VERSION 1.0
1. MAX()함수를 사용하여 CATEGORY 별로 MAX_PRICE를 추출했다
SELECT CATEGORY AS CATEGORY , MAX(PRICE) AS MAX_PRICE FROM FOOD_PRODUCT
GROUP BY CATEGORY
HAVING CATEGORY IN ('과자', '국' , '김치' , '식용유')
2. SELF JOIN 의 LEFT JOIN 활용하여 MAX_PRICE열과 PRICE가 같고, CATEGORY 가 같은것에 한해 LEFT JOIN 실행한다.
SELECT A.* , B.PRODUCT_NAME FROM
(SELECT CATEGORY AS CATEGORY , MAX(PRICE) AS MAX_PRICE FROM FOOD_PRODUCT
GROUP BY CATEGORY
HAVING CATEGORY IN ('과자', '국' , '김치' , '식용유')) A LEFT JOIN
(SELECT CATEGORY, PRODUCT_NAME , PRICE FROM FOOD_PRODUCT) B ON A.MAX_PRICE = B.PRICE AND A.CATEGORY = B.CATEGORY
ORDER BY MAX_PRICE DESC
728x90
반응형