테이블에 없는 값 만들기

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

프로그래머스

 

-- 코드를 입력하세요
SELECT HOUR ,SUM(COUNT)
FROM 
    (    
    SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR(DATETIME) UNION
    SELECT 0,0 UNION
    SELECT 1,0 UNION
    SELECT 2,0 UNION
    SELECT 3,0 UNION
    SELECT 4,0 UNION
    SELECT 5,0 UNION
    SELECT 6,0 UNION
    SELECT 7,0 UNION
    SELECT 8,0 UNION
    SELECT 9,0 UNION
    SELECT 10,0 UNION
    SELECT 11,0 UNION
    SELECT 12,0 UNION
    SELECT 13,0 UNION
    SELECT 14,0 UNION
    SELECT 15,0 UNION
    SELECT 16,0 UNION
    SELECT 17,0 UNION
    SELECT 18,0 UNION
    SELECT 19,0 UNION
    SELECT 20,0 UNION
    SELECT 21,0 UNION
    SELECT 22,0 UNION
    SELECT 23,0 
    ) TMP
GROUP BY HOUR
ORDER BY HOUR ASC;

 

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

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