자동차 대여 기록 별 대여 금액 구하기
- 할인 비율 계산하기
- 날짜 별 계산하기
- 날짜 차이 구하기
WITH WITH_RESULT AS (
SELECT A.DAILY_FEE, A.CAR_TYPE, B.HISTORY_ID,
DATEDIFF(end_date, start_date) + 1 AS PERIOD,
CASE
WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
ELSE 'NONE'
END AS 'DURATION_TYPE'
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.CAR_ID = B.CAR_ID
WHERE A.CAR_TYPE = '트럭'
)
SELECT A.HISTORY_ID,
ROUND(A.DAILY_FEE * A.PERIOD * (100 - IFNULL(B.DISCOUNT_RATE, 0)) / 100) AS 'FEE'
FROM WITH_RESULT A LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON B.DURATION_TYPE = A.DURATION_TYPE
AND B.CAR_TYPE = A.CAR_TYPE
ORDER BY 2 DESC, 1 DESC
프로그래머스 SQL 모의고사 1번
- UNION
- JOIN이 아닌 2개의 테이블을 하나로 합친다.
SELECT *
FROM (
SELECT *, 0 AS LIKES
FROM OLD_POSTS
UNION
SELECT *
FROM NEW_pOSTS
) A
ORDER BY ID DESC
