특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

[ji-0630]

문제 설명

[ji-0630]

나의 풀이

SELECT c.CAR_ID, c.CAR_TYPE, c.DAILY_FEE * p.DISCOUNT_RATE as "FEE"
from CAR_RENTAL_COMPANY_CAR c left outer join CAR_RENTAL_COMPANY_DISCOUNT_PLAN p 
on c.CAR_TYPE = p.CAR_TYPE
where c.CAR_TYPE in ("세단", "SUV")
and c.CAR_ID in (select CAR_ID
                from CAR_RENTAL_COMPANY_RENTAL_HISTORY
                where date_format(END_DATE, "%Y-%m-%d") < "2022-11-01"
                 or date_format(START_DATE, "%Y-%m-%d") > "2022-11-30"
                 group by CAR_ID)
and p.duration_type = "30일 이상"
order by FEE desc, c.CAR_TYPE, c.CAR_ID desc

• 틀렸음..

[ji-0630]

다른 사람의 풀이

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE, ROUND(A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100, 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV') 
AND (A.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m') AND DATE_FORMAT(END_DATE, '%Y-%m')))
AND (C.DURATION_TYPE = '30일 이상' AND (A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE) / 100) BETWEEN 500000 AND 2000000)
ORDER BY FEE DESC, A.CAR_TYPE ASC, A.CAR_ID DESC;

• not in으로 11월 없는 것 찾기
• 삼중 조인 방법 확인

[ji-0630]

다시 풀기

select c.CAR_ID, c.CAR_TYPE, round(DAILY_FEE*30*(100-DISCOUNT_RATE)/100) as "FEE" 
from CAR_RENTAL_COMPANY_CAR c join CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
on c.CAR_TYPE = p.CAR_TYPE
where c.CAR_ID not in (
    SELECT CAR_ID
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m') AND DATE_FORMAT(END_DATE, '%Y-%m'))
and c.CAR_TYPE in ('세단', 'SUV')
and p.DURATION_TYPE = "30일 이상"
and round(DAILY_FEE*30*(100-DISCOUNT_RATE)/100) between 500000 and 2000000