[프로그래머스SQL]입양 시각 구하기(2)

// 공통 테이블 표현식(Common Table Expression, CTE)을 
//사용하여 재귀적으로 시간대(HOURS)를 생성
//0부터 자기자신을 참조하며 23보다 작을때까지 반복
WITH RECURSIVE HOURS AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM HOURS WHERE HOUR < 23
)

SELECT HOURS.HOUR AS HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM HOURS
LEFT JOIN ANIMAL_OUTS ON DATE_FORMAT(DATETIME, '%H') = HOURS.HOUR
GROUP BY HOUR
ORDER BY HOUR;