[Programmers Lv.4] 자동차 대여 기록 별 대여 금액 구하기 - 151141

IFNULL, IF 사용하기 !

CASE를 쓰지 않아도 되니 코드가 확실히 간단해진다.

 

Question

 

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

 

Code (After : 25.06.) 

 

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

ORDER BY 2 DESC, 1 DESC

 

 

Code 1 : IFNULL , IF 사용.
JOIN 에서 Duration을 특정 글자로 리턴하여 duration_type과 비교.

 

WITH TRUCKS AS(
    SELECT C.CAR_ID, CAR_TYPE, 
            HISTORY_ID, DAILY_FEE, 
            DATEDIFF(END_DATE, START_DATE)+1 AS DURATION
        FROM CAR_RENTAL_COMPANY_CAR C
        JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
          ON C.CAR_ID = H.CAR_ID
        WHERE CAR_TYPE = '트럭'
)
SELECT HISTORY_ID, CAST((DAILY_FEE * DURATION) * (1-(IFNULL(D.DISCOUNT_RATE,0)/100)) AS SIGNED) AS FEE
    FROM TRUCKS T
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
      ON (T.CAR_TYPE = D.CAR_TYPE) AND 
        (IF(T.DURATION BETWEEN 7 AND 29, '7일 이상',
           IF(T.DURATION BETWEEN 30 AND 89, '30일 이상',
             IF(90 <= T.DURATION, '90일 이상','없음')))) = D.DURATION_TYPE
    ORDER BY 2 DESC, 1 DESC;

 

 

Code 2 : CASE 사용.

 

WITH TRUCKS AS(
    SELECT C.CAR_ID, CAR_TYPE, 
            HISTORY_ID, DAILY_FEE, 
            DATEDIFF(END_DATE, START_DATE)+1 AS DURATION,
            CASE
                WHEN DATEDIFF(END_DATE, START_DATE)+1<7 THEN '없음'
                WHEN DATEDIFF(END_DATE, START_DATE)+1<30 THEN '7일 이상'
                WHEN DATEDIFF(END_DATE, START_DATE)+1<90 THEN '30일 이상'
                ELSE '90일 이상'
            END AS DURATION_TYPE
        FROM CAR_RENTAL_COMPANY_CAR C
        JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
          ON C.CAR_ID = H.CAR_ID
        WHERE CAR_TYPE = '트럭'
)
SELECT HISTORY_ID, CAST((DAILY_FEE * DURATION) * (1-(IFNULL(D.DISCOUNT_RATE,0)/100)) AS SIGNED) AS FEE
    FROM TRUCKS T
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
      ON (T.CAR_TYPE = D.CAR_TYPE) AND (T.DURATION_TYPE = D.DURATION_TYPE)
    ORDER BY 2 DESC, 1 DESC;

 

now me

On my github