[GROUP BY] 입양 시각 구하기(2)

문제 설명

 

예시

 

해결

WITH RECURSIVE CTE AS (
    SELECT 0 AS NUM
    UNION ALL
    SELECT NUM+1
    FROM CTE
    WHERE NUM < 23
)
SELECT CTE.NUM HOUR, IFNULL(A.입양횟수, 0) COUNT
FROM CTE
LEFT JOIN (SELECT HOUR(datetime) 시간대, COUNT(*) 입양횟수
           FROM ANIMAL_OUTS
           GROUP BY 시간대
           ORDER BY 시간대) A
ON CTE.NUM = A.시간대;