[프로그래머스] JOIN - 특정 기간 동안 대여 가능한 자동차들

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

 

답을 생각하기 전에, 무턱대고 쿼리문부터 짜기 시작했다가 기간에서 펑크가 우선 났고,

이어서는 리스트는 맞게 뽑아놓고 요금을 바로 출력해내질 못해서 크게 두 번 수정.

 

JOIN도 JOIN이지만, NOT IN이냐, IN이냐 활용하는게 중요한 문제였다.

SELECT       C.CAR_ID
            ,C.CAR_TYPE
            ,C.DAILY_FEE*30 AS FEE
FROM         CAR_RENTAL_COMPANY_CAR C
WHERE        C.CAR_TYPE IN ('세단', 'SUV')
AND          C.CAR_ID IN (
                                SELECT       CAR_ID
                                FROM         CAR_RENTAL_COMPANY_RENTAL_HISTORY H
                                WHERE        (START_DATE > '2022-11-30'
                                OR           END_DATE   < '2022-10-01')
                         )
AND          C.CAR_ID IN (
                                SELECT       CAR_ID
                                FROM         CAR_RENTAL_COMPANY_CAR C
                                LEFT JOIN    CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
                                ON           C.CAR_TYPE = P.CAR_TYPE
                                WHERE        P.DURATION_TYPE = '30일 이상'
                                AND          C.DAILY_FEE * 30 * (100 - P.DISCOUNT_RATE)/100 BETWEEN 500000 AND 1000000
                         )

ORDER BY     3 DESC, 2, 1 DESC





----------수정본------

SELECT       C.CAR_ID
            ,C.CAR_TYPE
            ,FEE
FROM         CAR_RENTAL_COMPANY_CAR C
LEFT JOIN    (                                -----요금으로 필터링하기
                    SELECT       CAR_ID
                                ,FLOOR(C.DAILY_FEE * 30 * ((100 - P.DISCOUNT_RATE)/100)) AS FEE
                    FROM         CAR_RENTAL_COMPANY_CAR C
                    LEFT JOIN    CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
                    ON           C.CAR_TYPE = P.CAR_TYPE
                    WHERE        P.DURATION_TYPE = '30일 이상'
             ) F
USING        (CAR_ID)
WHERE        C.CAR_TYPE IN ('세단', 'SUV')
AND          C.CAR_ID
    NOT IN   (
                    SELECT       CAR_ID       ----기간으로 필터링하기
                    FROM         CAR_RENTAL_COMPANY_RENTAL_HISTORY H
                    WHERE        (START_DATE <= '2022-11-30'
                    AND           END_DATE   >= '2022-11-01')    #IN으로만 할 경우, 안 되는 되는 기간 바깥에 기록 하나라도 있으면 포함 되므로 NOT IN으로 풀 것!!!
             )
AND          FEE BETWEEN 500000 AND 2000000
ORDER BY     3 DESC, 2, 1 DESC

 

 

 

그래도 쿼리문을 예전과는 달리 깔끔하게 정리해서 작성하다보니 오류부분 발견하는 것도, 고치는 것도 금방인듯

 

아래는 예전에 풀었던 방식.

진짜 많이 늘긴 했다.

with temp as (
select h.car_id, c.car_type, c.daily_fee
from car_rental_company_rental_history h join car_rental_company_car c using(car_id)
where h.car_id not in (select distinct car_id
                       from car_rental_company_rental_history
                       where end_date >= '2022-11-01') and
      (c.car_type = '세단' or 'SUV')
    )
    
    
select t.car_id, t.car_type, round(t.daily_fee*30*(1 - p.discount_rate/100), 0) as fee, p.duration_type
from car_rental_company_discount_plan p join temp t using(car_type)
where p.duration_type = "30일 이상"  and
    t.daily_fee*30*(p.discount_rate/100) >= 500000 and t.daily_fee*30*(p.discount_rate/100) < 2000000;