[SQL][프로그래머스] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 - LEFT OUTER JOIN + SUBQUERY + GROUP BY MYSQL

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

프로그래머스

코드설명

LEFT OUTER JOIN + SUBQUERY + GROUP BY 를 활용합니다.

 

생각보다 아이디어를 만드는게 어려운 문제였습니다.

LEFT OUTER JOIN으로 대여 가능한 경우와 대여 중인 경우의 자동차를 모두 가져옵니다.

코드

코드1입니다.

SELECT CRCRH1.CAR_ID, 
    CASE 
        WHEN COUNT(CRCRH2.CAR_ID) = 0 THEN '대여 가능'
        ELSE '대여중'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRCRH1
LEFT OUTER JOIN ( select DISTINCT(car_id) from car_rental_company_rental_history where datediff('2022-10-16', start_date) >= 0 and datediff(end_date, '2022-10-16') >= 0) AS CRCRH2
ON CRCRH1.CAR_ID = CRCRH2.CAR_ID
GROUP BY CRCRH1.CAR_ID
ORDER BY CRCRH1.CAR_ID DESC

 

코드2입니다.

SELECT CRCRH1.CAR_ID, 
    CASE 
        WHEN CRCRH2.CAR_ID IS NULL THEN '대여 가능'
        ELSE '대여중'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRCRH1
LEFT OUTER JOIN ( select DISTINCT(car_id) from car_rental_company_rental_history where datediff('2022-10-16', start_date) >= 0 and datediff(end_date, '2022-10-16') >= 0) AS CRCRH2
ON CRCRH1.CAR_ID = CRCRH2.CAR_ID
GROUP BY CRCRH1.CAR_ID
ORDER BY CRCRH1.CAR_ID DESC

 

ORACLE

SELECT CRCRH.CAR_ID, 
    CASE
        WHEN CRCRH2.CAR_ID IS NULL THEN '대여 가능'
        ELSE '대여중'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
LEFT OUTER JOIN (
    SELECT DISTINCT(CAR_ID) AS CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE TO_CHAR(START_DATE,'YYYY-MM-DD') <= '2022-10-16' AND TO_CHAR(END_DATE,'YYYY-MM-DD') >= '2022-10-16'
) CRCRH2
ON CRCRH.CAR_ID = CRCRH2.CAR_ID
GROUP BY CRCRH.CAR_ID, CRCRH2.CAR_ID
ORDER BY CRCRH.CAR_ID DESC

 

GROUP BY 없이 DISTINCT로 처리해도 됩니다.

SELECT DISTINCT(CRCRH.CAR_ID),
    CASE
        WHEN CRCRH2.CAR_ID IS NULL THEN '대여 가능'
        ELSE '대여중'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
LEFT OUTER JOIN (
    SELECT DISTINCT(CAR_ID) AS CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE TO_CHAR(START_DATE,'YYYY-MM-DD') <= '2022-10-16' AND TO_CHAR(END_DATE,'YYYY-MM-DD') >= '2022-10-16'
) CRCRH2
ON CRCRH.CAR_ID = CRCRH2.CAR_ID
ORDER BY CRCRH.CAR_ID DESC