[Programmers] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT EXTRACT(MONTH FROM START_DATE) AS MONTH
     , CAR_ID
     , COUNT(HISTORY_ID) AS RECORDS
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
  WHERE CAR_ID IN (
                    SELECT CAR_ID
                      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                     WHERE TO_CHAR(START_DATE, 'YYYY-MM') 
                   BETWEEN '2022-08' AND '2022-10'
                     GROUP BY CAR_ID
                    HAVING COUNT(CAR_ID) >= 5  
  )
   AND TO_CHAR(START_DATE, 'YYYY-MM') 
                   BETWEEN '2022-08' AND '2022-10'
  GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
  ORDER BY MONTH, CAR_ID DESC

 

◈ EXTRACT 함수 사용

- 오라클에서 날짜유형의 데이터로부터 원하는 날짜 정보를 분리하여 추출할 수 있는 함수입니다.

SELECT EXTRACT('추출타입' FROM '추출할 컬럼) AS 별칭

▣ 예제1

SELECT EXTRACT(YEAR FROM SYSDATE) AS YEAR
  FROM DUAL