[Programmers] 151141번 - 자동차 대여 기록 별 대여 금액 구하기 [MySQL]

문제 링크

---

1. 문제 풀이

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 JOIN한 후 트럭만 필터링을 해줬다. 대여 기록 별 대여 금액은 스칼라 서브쿼리를 활용해서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN를 통해 계산해줬다.

---

2. 쿼리

SELECT
    h.HISTORY_ID,
    ROUND(c.DAILY_FEE * (DATEDIFF(h.END_DATE, h.START_DATE) + 1) * IFNULL(1 - (
        SELECT DISCOUNT_RATE
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND CAST(LEFT(DURATION_TYPE, LENGTH(DURATION_TYPE - 4)) AS UNSIGNED) <= (DATEDIFF(h.END_DATE, h.START_DATE) + 1)
        ORDER BY CAST(LEFT(DURATION_TYPE, LENGTH(DURATION_TYPE - 4)) AS UNSIGNED) DESC
        LIMIT 1
    ) / 100, 1)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS c
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS h
    ON c.CAR_ID = h.CAR_ID
WHERE c.CAR_TYPE = '트럭'
ORDER BY FEE DESC, h.HISTORY_ID DESC;

---