where절에 case when, sub쿼리

 

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

프로그래머스

 

 

SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE = (CASE WHEN CATEGORY="과자" THEN (SELECT MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY="과자")
                    WHEN CATEGORY="국" THEN (SELECT MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY="국")
                    WHEN CATEGORY="김치" THEN (SELECT MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY="김치")
                    WHEN CATEGORY="식용유" THEN (SELECT MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY="식용유")
            END
              )
GROUP BY CATEGORY
HAVING CATEGORY IN ("과자","국","김치","식용유")
ORDER BY MAX_PRICE DESC