Question

WITH문, INNER JOIN 사용

-- 코드를 입력하세요
WITH RAW AS (SELECT
a.AUTHORID,
a.AUTHORNAME,
b.CATEGORY,
(s.SALES* b.PRICE) AS TOTALSALES
FROM AUTHOR a
INNER JOIN BOOK b
ON a.AUTHORID = b.AUTHORID
INNER JOIN BOOKSALES s
ON b.BOOKID = s.BOOKID
WHERE DATEFORMAT(SALESDATE,'%Y-%m') = '2022-01'
)

SELECT
AUTHORID,
AUTHORNAME,
CATEGORY,
SUM(TOTALSALES) AS TOTALSALES
FROM RAW
GROUP BY 1,2,3
ORDER BY AUTHOR_ID ASC, CATEGORY DESC

작성중인 코드―solution.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 코드를 입력하세요
WITH RAW AS (SELECT
    a.AUTHOR_ID,
    a.AUTHOR_NAME,
    b.CATEGORY,
    (s.SALES* b.PRICE) AS TOTAL_SALES
FROM AUTHOR a
INNER JOIN BOOK b
ON a.AUTHOR_ID = b.AUTHOR_ID
INNER JOIN BOOK_SALES s
ON b.BOOK_ID = s.BOOK_ID
WHERE DATE_FORMAT(SALES_DATE,'%Y-%m') = '2022-01'
)

SELECT 
    AUTHOR_ID,
    AUTHOR_NAME,
    CATEGORY,
    SUM(TOTAL_SALES) AS TOTAL_SALES
FROM RAW 
GROUP BY 1,2,3
ORDER BY AUTHOR_ID ASC, CATEGORY DESC

Roy

2023.1.5 21:19

WITH문, INNER JOIN 사용

작성중인 코드―solution.sql

0 개의 답변

답변 쓰기

이 입력폼은 마크다운 문법을 지원합니다.