조건에 맞는 도서 리스트 출력하기 오라클(Oracle)
SELECT BOOK_ID AS BOOK_ID
, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE 1=1
AND TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021'
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE
재구매가 일어난 상품과 회원 리스트 구하기 오라클(Oracle)
SELECT USER_ID
, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC
오프라인/온라인 판매 데이터 통합하기 오라클(Oracle)
SELECT *
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, 'YYYYMM') = '202203'
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, 'YYYYMM') = '202203'
)
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID