[SQL 고득점 Kit][GROUP BY] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

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

프로그래머스

 

-- 코드를 입력하세요
SELECT car_id, concat('대여중') as AVAILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where date_format(start_date, '%Y-%m-%d') <= '2022-10-16' and date_format(end_date, '%Y-%m-%d') >= '2022-10-16'
group by car_id
union
SELECT car_id, concat('대여 가능') as AVAILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where (date_format(start_date, '%Y-%m-%d') > '2022-10-16' or date_format(end_date, '%Y-%m-%d') < '2022-10-16') and car_id not in (select car_id from CAR_RENTAL_COMPANY_RENTAL_HISTORY where date_format(start_date, '%Y-%m-%d') <= '2022-10-16' and date_format(end_date, '%Y-%m-%d') >= '2022-10-16' group by car_id)
group by car_id
order by car_id desc

 

case when 구문을 몰랐을 때, union을 사용해서 어거지로 풀었다...

아래는 case when 구문을 사용한 쿼리이다. case when 구문은 select에 사용하는 것이고,  조건에 따라서 어떻게 나타낼지를 구할 수 있다.

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