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

문제

MYSQL

내가 작성한 정답

select p.history_id, round(p.daily_fee*p.period*(1 - ifnull(d.discount_rate,0)*0.01),0) fee
from (select h.history_id, c.daily_fee, 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
        where c.car_type = '트럭') p
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
    on ((p.period < 7) and (d.duration_type is null)) or
        (p.period between 7 and 29) and (d.duration_type = '7일 이상') or
        (p.period between 30 and 89) and (d.duration_type = '30일 이상') or
        (p.period >= 90) and (d.duration_type ='90일 이상')
where d.car_type = '트럭' or d.car_type is null
order by 2 desc, 1 desc

ORACLE

내가 작성한 정답

select h.history_id, h.daily_fee*h.period*(1-nvl(d.discount_rate,0)*0.01) fee
from (select h.history_id, h.end_date-h.start_date+1 period, c.daily_fee
        from CAR_RENTAL_COMPANY_RENTAL_HISTORY h 
        join CAR_RENTAL_COMPANY_CAR c on h.car_id = c.car_id
        where c.car_type = '트럭') h
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
        on (((h.period between 7 and 29) and (d.duration_type = '7일 이상')) or
            ((h.period between 30 and 89) and (d.duration_type = '30일 이상')) or
            ((h.period >= 90) and (d.duration_type = '90일 이상')) or
            ((h.period) < 7 and (d.duration_type IS NULL)) )
where d.car_type = '트럭' or d.car_type is null
order by fee desc, history_id desc

이전에 작성한 정답

with a as (SELECT 
            max(case 
                when ((h.end_date-h.start_date)+1 >= 90) and (d.duration_type='90일 이상') then discount_rate
                when ((h.end_date-h.start_date)+1 >= 30) and (d.duration_type='30일 이상') then discount_rate
                when ((h.end_date-h.start_date)+1 >= 7) and (d.duration_type='7일 이상') then discount_rate
                else 0      end) as discount,
       h.history_id, c.daily_fee, (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
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.car_type = d.car_type
where c.car_type ='트럭'
group by h.history_id, c.daily_fee, (h.end_date-h.start_date)+1 )

select history_id, daily_fee*period*(100-discount)*0.01 fee
from a
order by fee desc ,history_id desc