1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 구할 값: 0시부터 23시까지, 각 시간대별로 입양 건 수
-- 정렬 조건: 시간대 순(오름차순)

WITH RECURSIVE time AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 
    FROM time 
    WHERE hour < 23
), animal AS (
    SELECT HOUR(datetime) AS hour
         , COUNT(*) AS count
    FROM animal_outs AS a 
    GROUP BY hour
    ORDER BY hour
)

SELECT time.hour
     , CASE WHEN animal.count IS NULL THEN 0 ELSE animal.count END AS count
FROM time
    LEFT JOIN animal ON time.hour = animal.hour
GROUP BY time.hour
ORDER BY time.hour