여러 날짜들 중 가장 최근 값으로 가져와 확인해야하기 때문에 MAX()를 사용했다.
SELECT CAR_ID,
MAX(CASE
WHEN (START_DATE<'2022-10-16' AND END_DATE <'2022-10-16')
OR (START_DATE>'2022-10-16' AND END_DATE >'2022-10-16') 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 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,
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;
아래와 같이 group을 쓰지 않고, '대여 가능', '대여중'이 나오게 하며 아래와 같은 테이블 결과가 나온다.
SELECT CAR_ID,
(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, START_DATE,END_DATE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
# GROUP BY CAR_ID
ORDER BY CAR_ID DESC
이처럼 MAX()를 사용하지 않으면 대여중인데도 대여가능으로 나오기 때문에 MAX()함수를 쓴 것이다!
CASE WHEN 조건1 THEN '조건1 반환값' WHEN 조건2 THEN '조건2 반환값' ELSE '충족되는 조건 없을 때 반환값' END
- seq값이 1~3이면 A, 4~6이면 B, 둘 다 아닐 경우 C를 출력하는 예제
SELECT seq, CASE WHEN (u.seq BETWEEN 1 AND 3) THEN 'A' WHEN (u.seq BETWEEN 4 AND 6) THEN 'B' ELSE 'C' END AS case_result FROM TABLE U