https://school.programmers.co.kr/learn/courses/30/lessons/151141
문제를 잘읽자..!
select history_id, (100-
(select discount_rate
from(
select nvl(max(discount_rate),0) discount_rate --dates가 7미만일 경우
from CAR_RENTAL_COMPANY_DISCOUNT_PLAN
where car_type = '트럭'
and REGEXP_REPLACE(DURATION_TYPE, '[^0-9]') <= x.dates --REGEXP_REPLACE 숫자만 빼기
order by 1
)
where rownum = 1)
) * x.dates * x.daily_fee/100 FEE
from (
select HISTORY_ID, a.car_id, (a.end_date-a.start_date)+1 dates, b.daily_fee --그냥 빼면 0인날이 되므로 하루만썼어도 하루되기위해 +1
from CAR_RENTAL_COMPANY_RENTAL_HISTORY a,
(SELECT car_id,daily_fee
from CAR_RENTAL_COMPANY_CAR
where car_type = '트럭') b
where a.car_id = b.car_id
) x
order by 2 desc, 1 desc