[SQL문제풀기] 자동차 대여 기록 별 대여 금액 구하기

문제

MYSQL

내가 작성한 정답

SELECT H.HISTORY_ID, FLOOR(H.DAILY_FEE*H.PERIOD*(100-IFNULL(D.DISCOUNT_RATE,0))*0.01) FEE
FROM (SELECT C.CAR_TYPE, C.DAILY_FEE, H.HISTORY_ID, 
      DATEDIFF(H.END_DATE,H.START_DATE)+1 PERIOD
        FROM CAR_RENTAL_COMPANY_CAR C
        JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
          ON C.CAR_ID = H.CAR_ID AND CAR_TYPE = '트럭') H
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
ON (H.CAR_TYPE = D.CAR_TYPE) AND
    ((H.PERIOD >= 90 AND D.DURATION_TYPE = '90일 이상') OR
    ((H.PERIOD < 90 AND H.PERIOD >= 30) AND D.DURATION_TYPE ='30일 이상') OR
    ((H.PERIOD BETWEEN 7 AND 29) AND D.DURATION_TYPE = '7일 이상') OR
    (H.PERIOD < 7 AND D.DURATION_TYPE IS NULL))
ORDER BY 2 DESC, 1 DESC;

ORACLE

내가 작성한 오답

WITH A AS (SELECT H.HISTORY_ID, C.CAR_ID, C.DAILY_FEE, C.CAR_TYPE,
                (H.END_DATE-H.START_DATE+1) PERIOD
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
            JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_ID = H.CAR_ID AND C.CAR_TYPE = '트럭')
SELECT A.HISTORY_ID, A.DAILY_FEE* A.PERIOD * (100- NVL(D.DISCOUNT_RATE,0))*0.01 FEE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
RIGHT JOIN A A ON  ((90<= A.PERIOD AND D.DURATION_TYPE ='90일 이상') 
            OR (30<= A.PERIOD AND D.DURATION_TYPE ='30일 이상')
            OR (7 <= A.PERIOD AND D.DURATION_TYPE = '7일 이상')
            OR (7 > A.PERIOD AND D.DURATION_TYPE IS NULL)) 
            AND D.CAR_TYPE = A.CAR_TYPE
ORDER BY 2 DESC, 1 DESC

내가 작성한 정답

WITH A AS (SELECT H.HISTORY_ID, C.CAR_ID, C.DAILY_FEE, C.CAR_TYPE,
                (H.END_DATE-H.START_DATE+1) PERIOD
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
            JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_ID = H.CAR_ID AND C.CAR_TYPE = '트럭')
SELECT A.HISTORY_ID, A.DAILY_FEE* A.PERIOD * (100- NVL(D.DISCOUNT_RATE,0))*0.01 FEE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
RIGHT JOIN A A ON  ((90<= A.PERIOD AND D.DURATION_TYPE ='90일 이상') 
            OR ((A.PERIOD BETWEEN 30 AND 89) AND D.DURATION_TYPE ='30일 이상')
            OR ((A.PERIOD BETWEEN 7 AND 29) AND D.DURATION_TYPE = '7일 이상')
            OR (7 > A.PERIOD AND D.DURATION_TYPE IS NULL)) 
            AND D.CAR_TYPE = A.CAR_TYPE
ORDER BY 2 DESC, 1 DESC