[MySQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 - JOIN

Saemi Min·2023년 2월 20일

mysql programmers

MySQL

목록 보기

14/21

Level 4

문제

해당 문제 링크

정답

SELECT A.CAR_ID, A.CAR_TYPE, FLOOR(A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30) AS FEE FROM CAR_RENTAL_COMPANY_CAR A
RIGHT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B ON A.CAR_TYPE = B.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV') AND B.duration_type = '30일 이상' AND A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30>=500000 AND A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30<2000000
AND A.CAR_ID NOT IN (
    SELECT CAR_ID FROM (
        SELECT C.CAR_ID FROM CAR_RENTAL_COMPANY_CAR C
            RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY D ON C.CAR_ID = D.CAR_ID
        WHERE (D.START_DATE <"2022-12-01" AND D.END_DATE>="2022-11-01")
    ) E 
)
ORDER BY FEE DESC, A.CAR_TYPE ASC ,A.CAR_ID DESC

풀이

내부의 SELECT 문

일단 대여 가능한지 여부를 찾는 서브 쿼리문이다!

SELECT CAR_ID FROM (
        SELECT C.CAR_ID FROM CAR_RENTAL_COMPANY_CAR C
            RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY D ON C.CAR_ID = D.CAR_ID
        WHERE (D.START_DATE <"2022-12-01" AND D.END_DATE>="2022-11-01")
    ) E

이 코드는 이전에 그룹별 조건에 맞는 식당 목록 출력하기 - JOIN 문제를 풀면서 서브 쿼리문은 이렇게 쓰면 되는구나를 알고 작성할 수 있었다.

외부의 SELECT 문

대여가 가능한 자동차만 서브쿼리문을 통해 처리해 보여줬기 때문에 남은 조건들을 처리하면 됐다!

자동차 종류가 '세단' 또는 'SUV' 인 자동차만 나오게 하고,
30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차만 나오게 했다.

테이블 구조에 따라 JOIN, LEFT JOIN이 아닌 RIGHT JOIN으로 했다.

결과로 내보내야 하는 열 중 FEE 는 대여 금액을 알아야 했기에 서브 쿼리문에서 처리하는 것이 아닌 외부 쿼리문을 통해 보여주도록 했다.

초반에는 이런 구조로 짰지만, FEE 금액을 알아내기 어려움이 있어, 순서를 바꿨더니 그제야 잘 접근했다는 것을 알 수 있었다.

# SELECT A.CAR_ID, A.CAR_TYPE, A.DAILY_FEE AS FEE, B.START_DATE, B.END_DATE FROM CAR_RENTAL_COMPANY_CAR A
# RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B ON A.CAR_ID = B.CAR_ID
# WHERE A.CAR_TYPE IN ('세단', 'SUV') AND (B.END_DATE <"2022-11-01" OR B.START_DATE>"2022-11-01")
# AND A.DAILY_FEE IN (
#     SELECT DAILY_FEE FROM (
#         SELECT (C.DAILY_FEE*((100-D.DISCOUNT_RATE)/100))*30 AS VAL FROM CAR_RENTAL_COMPANY_CAR C
#             RIGHT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
#         WHERE D.duration_type = '30일 이상' AND (C.DAILY_FEE*((100-D.DISCOUNT_RATE)/100))*30>=500000 AND (C.DAILY_FEE*((100-D.DISCOUNT_RATE)/100))*30<2000000
#     ) E 
# )

접근 및 시도한 코드, 조심해야 할 점

여러번 코드 작성 후 그나마 정답에 가깝게 나온 것이 아래 코드이다. (정답 아님)

SELECT A.CAR_ID, A.CAR_TYPE, FLOOR(A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30) AS FEE FROM CAR_RENTAL_COMPANY_CAR A
RIGHT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B ON A.CAR_TYPE = B.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV') AND B.duration_type = '30일 이상' AND A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30>=500000 AND A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30<2000000
AND A.CAR_ID IN (
    SELECT CAR_ID FROM (
        SELECT C.CAR_ID FROM CAR_RENTAL_COMPANY_CAR C
            RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY D ON C.CAR_ID = D.CAR_ID
        WHERE (D.END_DATE <"2022-11-01" OR D.START_DATE>"2022-11-30")
    ) E 
)
ORDER BY FEE DESC, A.CAR_TYPE ASC ,A.CAR_ID DESC

정답과 다른 부분은 아래와 같다.

AND A.CAR_ID IN (
...
		WHERE (D.END_DATE <"2022-11-01" OR D.START_DATE>"2022-11-30")
    ) E

실제 정답 부분은 아래와 같다.

AND A.CAR_ID NOT IN (
...
		WHERE (D.START_DATE <"2022-12-01" AND D.END_DATE>="2022-11-01")
    ) E

앞서 작성한 코드가 틀린 이유는 만약 D.END_DATE <"2022-11-01" 조건은 만족하지만, D.START_DATE가 11-30보다 더 앞인 11-26일 빌린다면 문제가 발생한다. OR이기 때문이다.
그렇기 때문에 아예 11-1~11-30일이 되는 날짜들을 구한 뒤 이를 제외하는 식으로 작성해야한다. 아래 그림을 보면 더 쉽게 이해할 수 있다.