[SQL] 프로그래머스 [Level-4] 오프라인/온라인 판매 데이터 통합하기

<MySQL>

SELECT A.SALES_DATE
     , A.PRODUCT_ID
     , A.USER_ID
     , A.SALES_AMOUNT
  FROM (
         SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
              , PRODUCT_ID
              , USER_ID
              , SALES_AMOUNT
           FROM ONLINE_SALE
          WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
      UNION ALL 
         SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
              , PRODUCT_ID
              , NULL AS USER_ID
              , SALES_AMOUNT
           FROM OFFLINE_SALE
          WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
       ) A
ORDER BY A.SALES_DATE, A.PRODUCT_ID, A.USER_ID

<Oracle>

SELECT A.SALES_DATE
     , A.PRODUCT_ID
     , A.USER_ID
     , A.SALES_AMOUNT
  FROM (
         SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
              , PRODUCT_ID
              , USER_ID
              , SALES_AMOUNT
           FROM ONLINE_SALE
          WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
      UNION ALL 
         SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
              , PRODUCT_ID
              , NULL AS USER_ID
              , SALES_AMOUNT
           FROM OFFLINE_SALE
          WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
       ) A
ORDER BY A.SALES_DATE, A.PRODUCT_ID, A.USER_ID

 

 

 

<NULL 값을 먼저 또는 나중에 출력되도록 정렬 방법>

MySQL : ORDER BY 컬럼 IS NULL (ASC / DESC)

Oracle : ORDER BY 컬럼 (ASC / DESC) NULLS (FIRST / LAST)

 

프로그래머스 오프라인/온라인 판매 데이터 통합하기 SQL