[SQL문제풀기] 저자 별 카테고리 별 매출액 집계하기

문제

MYSQL

내가 작성한 정답

WITH BOOK_TB AS (
    SELECT B.*,A.SALES_DATE,A.SALES
    FROM BOOK B
    LEFT JOIN BOOK_SALES A ON B.BOOK_ID = A.BOOK_ID
)
SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,SUM(B.PRICE*B.SALES) TOTAL_SALES
FROM BOOK_TB B
JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE SALES_DATE LIKE '2022-01%'
GROUP BY A.AUTHOR_ID,B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC;

ORACLE

내가 작성한 정답

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,SUM(B.PRICE*B.SALES) TOTAL_SALES
FROM AUTHOR A
JOIN (SELECT C.CATEGORY, C.AUTHOR_ID, C.PRICE, D.SALES_DATE, D.SALES
      FROM BOOK C
      JOIN BOOK_SALES D ON D.BOOK_ID = C.BOOK_ID
     ) B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE TO_CHAR(B.SALES_DATE,'YYMM') = '2201'
GROUP BY A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC