#51. GROUP BY : 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. MAX

SELECT 
    CAR_ID, 
    MAX(CASE 
            WHEN '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') 
            AND DATE_FORMAT(END_DATE, '%Y-%m-%d')
        THEN '대여중'
        ELSE '대여 가능'
        END) AS 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

SELECT 
    CAR_ID, 
    MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE
        THEN '대여중' ELSE '대여 가능'
        END) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

 

 

2. SUBQUERY

SELECT CAR_ID, 
    CASE 
        WHEN CAR_ID IN (
    	SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) 
    THEN '대여중'
    ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

 

 

코드 설명

#SELECT #MAX #DATE_FORMAT #CASE #WHERE #BETWEEN #GROUP BY #ORDER BY #SUBQUERY

 

 

문제 출처

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