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

silver

May 07, 2025

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

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

MYSQL

내가 작성한 오답

: NULL으로 출력되어야한다고 생각해 ‘NULL’값을 넣었으나 값이 없음을 나타내는 null을 넣어야했다.


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;