![[SQL문제풀기] 자동차 대여 기록 별 대여 금액 구하기](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2025-01-19T10%3A41%3A53.616Z-6983c0e0-e7ad-4013-99f8-d092e5d68ece&w=3840&q=75)
문제
MYSQL
내가 작성한 정답 1
with a as (select h.history_id, c.car_id, c.car_type, c.daily_fee, h.start_date,
h.end_date, datediff(end_date,start_date)+1 period,
case
when datediff(end_date,start_date)+1 >= 90 then duration_type = '90일 이상'
when datediff(end_date,start_date)+1 >= 30 then duration_type = '30일 이상'
when datediff(end_date,start_date)+1 >= 7 then duration_type = '7일 이상'
else 0 end as bool,
case
when datediff(end_date,start_date)+1 < 7 then 0
else d.discount_rate end as discount
from CAR_RENTAL_COMPANY_CAR c
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.CAR_TYPE = d.CAR_TYPE
join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id)
select history_id, round(period*(100-discount)*0.01*daily_fee) fee
from a
where car_type = '트럭' and (bool = 1 or period < 7)
group by history_id, round(period*(100-discount)*0.01*daily_fee)
order by 2 desc, 1 desc내가 작성한 정답 2
: 첫번째 작성한 정답에서 7일미만의 기간은 출력값이 같아도 history_id 별 레코드가 3개 나와 group by를 해줘야하기 때문에 bool로 정의한 컬럼과 discount로 정의한 컬럼을 하나로 합쳤다.
with a as (select h.history_id, c.car_id, c.car_type, c.daily_fee, d.discount_rate,
h.start_date, h.end_date, datediff(h.end_date,h.start_date)+1 period,
case
when datediff(h.end_date,h.start_date)+1 >= 90 and (d.duration_type = '90일 이상') then d.discount_rate
when datediff(h.end_date,h.start_date)+1 >= 30 and (d.duration_type = '30일 이상') then d.discount_rate
when datediff(h.end_date,h.start_date)+1 >= 7 and (d.duration_type = '7일 이상') then d.discount_rate
else 0 end as discount
from CAR_RENTAL_COMPANY_CAR c
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN d on c.CAR_TYPE = d.CAR_TYPE
join CAR_RENTAL_COMPANY_RENTAL_HISTORY h on c.car_id = h.car_id)
select history_id, min(round(period*(100-discount)*0.01*daily_fee)) fee
from a
where car_type = '트럭'
group by history_id
order by 2 desc, 1 descORACLE
내가 작성한 정답 1
with a as (SELECT 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 ='트럭')
select history_id, round(daily_fee*period*(100-discount)*0.01) fee
from (select history_id, daily_fee, period, max(discount) discount
from a group by history_id, daily_fee, period)
order by fee desc, history_id desc: mysql에서 작성한 정답 2처럼 min을 사용한다면 서브쿼리를 사용하지 않아도 된다.
select history_id, min(round(period*(100-discount)*0.01*daily_fee)) fee
from a
group by history_id
order by fee desc, history_id desc내가 작성한 정답 2
: 서브쿼리를 사용하지 않기 위해 위의 작성한 정답의 cte에 max를 넣어 최대값만 구해서 가져왔다.
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 descShare article