[프로그래머스] 자동차 대여 기록 별 대여 금액 구하기

문제

제출

 select a.history_id, round(case when c.discount_rate is not null then b.daily_fee*(1-c.DISCOUNT_RATE/100)*(datediff(end_date,start_date)+1)
                           else b.daily_fee*(datediff(end_date,start_date)+1) end,0) as FEE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY a
join CAR_RENTAL_COMPANY_CAR b on a.car_id = b.car_id
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN c
on b.car_type = c.car_type and
((datediff(end_date,start_date)+1 >= 90 and c.DURATION_TYPE='90일 이상') or
(datediff(end_date,start_date)+1 between 29 and 90 and c.DURATION_TYPE='30일 이상') or
(datediff(end_date,start_date)+1 between 6 and 30 and c.DURATION_TYPE='7일 이상'))
where b.car_type = '트럭'
order by 2 desc, 1 desc

또 다른 답

select a.history_id, round(b.daily_fee*(1-ifnull(c.discount_rate,0)/100)*(datediff(end_date,start_date)+1),0) FEE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY a
join CAR_RENTAL_COMPANY_CAR b on a.car_id = b.car_id
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN c
on b.car_type = c.car_type and
((datediff(end_date,start_date)+1 >= 90 and c.DURATION_TYPE='90일 이상') or
(datediff(end_date,start_date)+1 between 29 and 90 and c.DURATION_TYPE='30일 이상') or
(datediff(end_date,start_date)+1 between 6 and 30 and c.DURATION_TYPE='7일 이상'))
where b.car_type = '트럭'
order by 2 desc, 1 desc

case when 으로 c.discount_rate 가 null일 때 처리를 Ifnull로 간단하게 표현했다.