(Programmers) SQL 풀이 (9)

✏️ Programmers MySQL 정답률 66~68% 전체 풀이

1️⃣ 5월 식품들의 총매출 조회하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131117)

SELECT FO.PRODUCT_ID, FP.PRODUCT_NAME, (FP.PRICE * SUM(FO.AMOUNT)) AS TOTAL_SALES
FROM FOOD_PRODUCT AS FP
JOIN (
    SELECT *
    FROM FOOD_ORDER
    WHERE MONTH(PRODUCE_DATE) = 5
) AS FO
ON FP.PRODUCT_ID = FO.PRODUCT_ID
GROUP BY FP.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, FO.PRODUCT_ID;

⬇️

# join 하는 sub 테이블 간소화
SELECT FP.PRODUCT_ID, PRODUCT_NAME, (PRICE * TOTAL) AS TOTAL_SALES
FROM FOOD_PRODUCT AS FP
JOIN (
    SELECT PRODUCT_ID, SUM(AMOUNT) AS TOTAL
    FROM FOOD_ORDER
    WHERE MONTH(PRODUCE_DATE) = 5
    GROUP BY PRODUCT_ID
) AS FO
ON FP.PRODUCT_ID = FO.PRODUCT_ID
GROUP BY PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, FP.PRODUCT_ID;

2️⃣ 식품분류별 가장 비싼 식품의 정보 조회하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131116)

SELECT FP.CATEGORY, MX AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT AS FP
JOIN (
    SELECT CATEGORY, MAX(PRICE) AS MX
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
) AS FS
ON FP.CATEGORY = FS.CATEGORY AND FP.PRICE = FS.MX
ORDER BY MAX_PRICE DESC;

유사한 문제 : https://school.programmers.co.kr/learn/courses/30/lessons/131123