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

✅ 코드

WITH RECURSIVE cte AS (
   SELECT 0 AS num
    
   UNION ALL
    
   SELECT num + 1
   FROM cte
   WHERE num < 23
), ANIMAL_OUTS_REFACTOR AS (
   SELECT HOUR(DATETIME) AS HOUR
       , COUNT(ANIMAL_ID) AS CNTS
   FROM ANIMAL_OUTS
   GROUP BY HOUR
   ORDER BY HOUR
)

SELECT cte.num
    , IFNULL(O.CNTS, 0)
FROM cte
   LEFT JOIN ANIMAL_OUTS_REFACTOR AS O ON cte.num = O.HOUR

WITH RECURSIVE CTE문을 사용한 풀이입니다.

NULL값을 다루기 위해서 IFNULL 함수를 사용했습니다.