임시 테이블을 불러올 때 사용하던 WITH와 함께 작성
테이블들을 합쳐주는 UNION을 사용
WITH RECURSIVE TEMP_NAME (Col1, Col2, …) AS(
Non_RECURSIVE QUREY
UNION ALL
RECURSIVE QUREY
)
SELECT COL1, COL2
FROM TEMP_NAME
WITH RECURSIVE tmp1 AS(
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM tmp1
WHERE HOUR < 23)
SELECT tmp1.HOUR
FROM tmp1
JOIN절을 통해 병합
--
WITH RECURSIVE tmp1 AS(
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM tmp1
WHERE HOUR < 23)
SELECT tmp1.HOUR, IFNULL(US.count,0)
FROM tmp1
LEFT JOIN (SELECT HOUR(datetime) AS hour, count(DISTINCT(animal_id)) as count
FROM animal_outs
GROUP BY 1) AS US ON tmp1.HOUR = US.hour