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

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

어제에 이어 간만에 풀었던 재귀 문제.

재귀를 어떻게 하는 것인지 알고만 있으면 크게 어려울 건 없는듯

 

WITH RECURSIVE TMP AS (
        SELECT       0 AS HOUR
        UNION
        SELECT       HOUR + 1
        FROM         TMP
        WHERE        HOUR < 23
    )
    
SELECT       HOUR
            ,COUNT(ANIMAL_ID) AS COUNT
FROM         TMP
LEFT JOIN    ANIMAL_OUTS
ON           SUBSTR(DATETIME, 12, 2) = HOUR
GROUP BY     HOUR

 

 

 

with recursive temp as (
select 0 as hour
union all
select hour + 1
from temp
where hour <23)


select t.hour, count(animal_id) as count
from temp t left join animal_outs a on t.hour = hour(a.datetime)
group by t.hour
order by t.hour