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

 

 

 

 

나의 풀이

SELECT SALES_DATE,PRODUCT_ID,USER_ID,SALES_AMOUNT FROM ONLINE_SALE
WHERE SALES_DATE = 2022-03-01
ORDER BY SALES_DATE,PRODUCT_ID,USER_ID,SALES_AMOUNT;

여기까지 하다가 날짜 포맷 몰라서 찾아봄

 

 

날짜포맷 사용해서 다시 풀이

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID,USER_ID,SALES_AMOUNT FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, NULL USER_ID, SALES_AMOUNT FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'

ORDER BY SALES_DATE,PRODUCT_ID,USER_ID;

 

 

 

---

 

다른사람들의 풀이

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") SALES_DATE,
    PRODUCT_ID,
    USER_ID,
    SALES_AMOUNT
FROM ONLINE_SALE
WHERE sales_date >= '2022-03-01' and sales_date < '2022-04-01'

UNION ALL

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") SALES_DATE,
    PRODUCT_ID,
    NULL AS USER_ID,
    SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE sales_date >= '2022-03-01' and sales_date < '2022-04-01'
    
ORDER BY SALES_DATE , PRODUCT_ID , USER_ID

AND로 기간을 설정해준 경우

UNION으로 두 테이블을 합쳐준다

 

 

(SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE AS N
WHERE SALES_DATE LIKE '2022-03%'
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE AS F
WHERE SALES_DATE LIKE '2022-03%')
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

UNION으로 합쳐줌

LIKE 문자열 패턴 검색

% : 모든 문자 검색

_ : 한글자 검색

 

 

 

SELECT date_format(SALES_DATE,'%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE year(SALES_DATE) = 2022 AND month(SALES_DATE) = 3

UNION ALL

SELECT date_format(SALES_DATE,'%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE year(SALES_DATE) = 2022 AND month(SALES_DATE) = 3

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

YYYY와 MM을 직접 설정해서 가져옴

 

 

 

---

JS공부할 때랑 느낌이 또 다르네 명령문만 잘 알면 써먹을 수 있을거 같다