[MySQL] 프로그래머스 131117. 5월 식품들의 총매출 조회하기

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

프로그래머스

정답 코드

SELECT FOOD_ORDER.PRODUCT_ID, FOOD_PRODUCT.PRODUCT_NAME, FOOD_PRODUCT.PRICE * SUM(FOOD_ORDER.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER JOIN FOOD_PRODUCT ON FOOD_PRODUCT.PRODUCT_ID = FOOD_ORDER.PRODUCT_ID
WHERE PRODUCE_DATE LIKE '2022-05%'
GROUP BY FOOD_ORDER.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, FOOD_ORDER.PRODUCT_ID;

WITH TEMP AS (
    SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
    FROM FOOD_ORDER
    WHERE PRODUCE_DATE LIKE '2022-05%' # year(PRODUCE_DATE) = 2022 and month(PRODUCE_DATE) = 5   
    GROUP BY PRODUCT_ID
)

SELECT TEMP.PRODUCT_ID, FOOD_PRODUCT.PRODUCT_NAME, TEMP.AMOUNT * FOOD_PRODUCT.PRICE AS TOTAL_SALES
FROM TEMP
JOIN FOOD_PRODUCT ON TEMP.PRODUCT_ID = FOOD_PRODUCT.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, TEMP.PRODUCT_ID;

.