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

1. 문제

프로그래머스

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

---

2. 풀이

1.  자주 사용하는 컬럼들, 계산값들을 모아두기 위해 서브쿼리를 사용한다. history_id, end_date-start_date+1, daily_fee를 셀렉한다. 이 때, car_type='트럭'인 것만 골라서 조회해야 한다.

2.  case문을 이용한다. 90일 이상, 30일 이상, 7일 이상, 그 이하 순서로 when 조건을 걸어야 한다. (혹은 아예 그 반대로)

3.  '대여기간*하루대여료*(100-할인율)/100한 값'을 반올림해서 fee 컬럼명으로 만든다. 반올림은 round(값,0)하면 정수부분만 나오는 걸 이용한다. 참고로, round(값,1)하면 소수첫째자리까지 나온다.

3-1.  할인율을 조회할때 서브쿼리를 활용한다. 조건에 맞는 duration_type과 car_type이 트럭이 맞는지 확인한 후, discount_rate를 구한다.

SELECT HISTORY_ID, 
    (CASE
     WHEN DAYS >= 90
     THEN ROUND(DAYS*DAILY_FEE
                *(100-(SELECT DISCOUNT_RATE 
                       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                       WHERE DURATION_TYPE='90일 이상' AND CAR_TYPE = '트럭'))
                /100
            ,0)
     WHEN DAYS >= 30
     THEN ROUND(DAYS*DAILY_FEE
                *(100-(SELECT DISCOUNT_RATE 
                       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                       WHERE DURATION_TYPE='30일 이상' AND CAR_TYPE = '트럭'))
                /100
            ,0)
     WHEN DAYS >= 7
     THEN ROUND(DAYS*DAILY_FEE
                *(100-(SELECT DISCOUNT_RATE
                       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                       WHERE DURATION_TYPE='7일 이상' AND CAR_TYPE = '트럭'))
                /100
            ,0)
     ELSE DAILY_FEE * DAYS
    END) AS FEE
FROM (SELECT H.HISTORY_ID, DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DAYS, C.DAILY_FEE
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        JOIN CAR_RENTAL_COMPANY_CAR C
        USING(CAR_ID)
        WHERE C.CAR_TYPE = '트럭'
     ) AS SUB
ORDER BY FEE DESC, HISTORY_ID DESC

---

3. 참고사항

✨✨✨ CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블의 DISCOUNT_RATE의 타입은 STRING이 아니다!!

이런 식으로 되어 있어서 문자열인줄 알았지만, (그래서 SUBSTR(DISCOUNT_RATE,-1)한 다음 CAST했었다..)

이렇게 테이블 구조를 보면 INTEGER타입이란 걸 알 수 있다. 이 것 때문에 한 시간은 넘게 붙잡고 있었으니 참고할 것..