재귀 쿼리

- 반복적으로 작동되는 쿼리

- 자료들의 계층을 파악하는데 용이하다.

 

구조

WITH RECURSIVE cte AS (
    -- NON-RECURSIVE 문, 처음 한 번 실행됨 (Anchor Member)
    SELECT 1 AS n
    -- 반드시 UNION 사용해야 함
    UNION ALL
    -- RECURSIVE 문 (Recursive Member)
    -- 서브 쿼리에서 가상 테이블 참조하는 문장 필요
    SELECT n + 1
    FROM cte
    -- Termination Condition (종료 조건)
    WHERE n < 10
)
SELECT * FROM cte

 

예시

2011년 ~ 2020년 출력하기

WITH RECURSIVE cte2 AS (
	SELECT 2011 AS yyyy
	UNION ALL
	SELECT yyyy + 1 FROM cte2
	WHERE yyyy < 2020
)
SELECT yyyy FROM cte2

 

2020년 1월 1일 ~ 2020년 2월 28일 출력하기

WITH RECURSIVE cte3 AS (
	SELECT DATE_FORMAT('2020-01-01', '%Y-%m-%d') AS dt FROM DUAL
	UNION ALL
	SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM cte3
	WHERE dt < '2020-02-28'
)
SELECT * FROM cte3

 

예시 문제

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

프로그래머스

풀이 1

WITH RECURSIVE cte AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM cte
    WHERE HOUR < 23
), tmp AS (
    SELECT 
        CAST(DATE_FORMAT(DATETIME, '%H') AS UNSIGNED) AS HOUR
        , COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
)

SELECT
    cte.HOUR
    , IFNULL(tmp.COUNT, 0) AS COUNT
FROM cte
LEFT JOIN tmp ON tmp.HOUR = cte.HOUR

 

풀이 2

WITH RECURSIVE cte AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM cte
    WHERE HOUR < 23
)

SELECT
    cte.HOUR
    , COUNT(DATETIME) AS COUNT
FROM cte
LEFT JOIN ANIMAL_OUTS AS ao ON cte.HOUR = HOUR(ao.DATETIME)
GROUP BY cte.HOUR

 

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

프로그래머스

풀이

WITH RECURSIVE TMP AS (
    SELECT ID
        , PARENT_ID
        , 1 AS GEN
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT
        e.ID
        , e.PARENT_ID
        , (t.GEN + 1) AS GEN
    FROM ECOLI_DATA e
    INNER JOIN TMP t ON e.parent_id = t.id
)
SELECT id
FROM TMP
WHERE GEN = 3

 

출처

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

[MYSQL] 📚 RECURSIVE (재귀 쿼리)

https://allmana.tistory.com/144

[MySql] 임시 테이블 1부터 10까지

https://velog.io/@glad415/MySQL-RECURSIVE-QUREY-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

[MySQL] RECURSIVE QUREY (재귀 쿼리)