[MySQL] recursive query

문제

프로그래머스 문제: 링크

With Clause 를 이용한 풀이

참고 자료: MySQL doc on With (common table expressions)

1. with clause를 사용하면 SubQuery를 따로 정의할 수 있어 가독성이 좋고, 편리하다.
2. with clause 의 기본 구조

WITH 
	cte1 AS (SELECT a, b FROM table1)
    cte2 AS (SELECT c, d FROM table2)

SELECT b, d
FROM cte1
JOIN cte2
WHERE cte1.a=cte2.c
;

• common table expression, cte?

  A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.

3. Recursive 할 때 주의점

• recursive할 때, WITH Clause 안의 cte가 스스로를 refer하면 꼭 WITH RECURSIVE를 붙여줘야 한다.

문제 풀이

WITH RECURSIVE TEMP
AS (SELECT 0 AS HOUR   
    UNION ALL
    SELECT HOUR+1 FROM TEMP
    WHERE HOUR<23) #with를 이용한 recursive 만들기
    
SELECT HOUR, COUNT(ANIMAL_OUTS.ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
RIGHT JOIN TEMP
ON HOUR(ANIMAL_OUTS.DATETIME) = TEMP.HOUR
GROUP BY HOUR