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

 

문제

 

 

개념

• RECURSIVE(재귀 CTE)
  • 구조

WITH RECURSIVE cte (n) AS 
(
  SELECT 1 -- 시작 
  UNION ALL
  SELECT n + 1 -- 반복
  FROM cte 
  WHERE n < 5 -- 마지막
)
SELECT * FROM cte;

 

 

정답

WITH RECURSIVE CTE(N) AS (
    SELECT 0
    UNION ALL
    SELECT N + 1
    FROM CTE
    WHERE N < 23
)
SELECT N AS HOUR, COUNT(ANIMAL_ID)
FROM CTE C LEFT JOIN ANIMAL_OUTS A ON C.N = HOUR(A.DATETIME)
GROUP BY 1
ORDER BY 1