[SQL문제풀기] 오프라인/온라인 판매 데이터 통합하기

문제

MYSQL

내가 작성한 오답

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE,'%y%m') = '2203' 
UNION
SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE, PRODUCT_ID, 'NULL' USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE,'%y%m') = '2203' 
ORDER BY 1, 2, 3;

내가 작성한 정답1

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE,'%y%m') = '2203' 
UNION
SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE, PRODUCT_ID, NULL USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE,'%y%m') = '2203' 
ORDER BY 1, 2, 3;

내가 작성한 정답2

WITH A AS (SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
            FROM ONLINE_SALE
            UNION
            SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE, PRODUCT_ID, NULL USER_ID, SALES_AMOUNT
            FROM OFFLINE_SALE)
SELECT * 
FROM A 
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
ORDER BY 1, 2, 3;

ORACLE

내가 작성한 정답

WITH SALES AS (SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
                FROM ONLINE_SALE
                UNION 
                SELECT SALES_DATE, PRODUCT_ID, NULL USER_ID, SALES_AMOUNT
                FROM OFFLINE_SALE)
SELECT TO_CHAR(SALES_DATE,'YYYY-MM-DD') SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM SALES
WHERE TO_CHAR(SALES_DATE,'YYMM') = '2203'
ORDER BY 1,2,3;