[프로그래머스] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(MySQL)

 

 

목차

 

 

1. 문제

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

프로그래머스

 

2. 해결 과정

1. START_DATE 와 END_DATE가 2022-11-01 ~ 2022-11-30 사이가 아닌 것.

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
AND END_DATE BETWEEN '2022-11-01' AND '2022-11-30'

• 처음에 이렇게 생각했는데, 이 경우에는 저 기간 사이에 겹쳐져 있는 경우는 제외가 되지 않는 문제가 있다.
  • ex) 2022-10-20 ~ 2022-11-10

 

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-11-30'
AND END_DATE >= '2022-11-01'

• 위와 같이 변경해줘야 한다.

 

2. 30일간의 대여금액이 50만원 이상 200만원 미만인 자동차

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(30 
		* (C.DAILY_FEE * (1-(IFNULL(P.DISCOUNT_RATE,0)/100))),0) 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일 이상"
AND (ROUND(30 
		* (C.DAILY_FEE * (1-(IFNULL(P.DISCOUNT_RATE,0)/100))),0) BETWEEN 500000 AND 1999999)
AND (C.CAR_TYPE = "세단" OR C.CAR_TYPE = "SUV")
ORDER BY 3 DESC, C.CAR_TYPE ASC, C.CAR_ID DESC;

 

 

3. 2에 1에서 구한 값을 포함하기 위해 CTE로 변경.

WITH EXCEPT_CAR AS (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-11-30'
AND END_DATE >= '2022-11-01')

 

 

3. 최종 코드

# START_DATE 와 END_DATE가 2022-11-01 ~ 2022-11-30 사이가 아닌 것.

WITH EXCEPT_CAR AS (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= '2022-11-30'
AND END_DATE >= '2022-11-01')

# 30일간의 대여금액이 50만원 이상 200만원 미만인 자동차

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(30 
		* (C.DAILY_FEE * (1-(IFNULL(P.DISCOUNT_RATE,0)/100))),0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C LEFT JOIN EXCEPT_CAR E
ON C.CAR_ID = E.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE P.DURATION_TYPE = "30일 이상"
AND C.CAR_ID NOT IN (SELECT CAR_ID FROM EXCEPT_CAR)
AND (ROUND(30 
		* (C.DAILY_FEE * (1-(IFNULL(P.DISCOUNT_RATE,0)/100))),0) BETWEEN 500000 AND 1999999)
AND (C.CAR_TYPE = "세단" OR C.CAR_TYPE = "SUV")
ORDER BY 3 DESC, C.CAR_TYPE ASC, C.CAR_ID DESC;