[MySQL] WITH, WITH RECURSIVE 구문

WITH 구문

with 구문은 메모리 상에 가상의 테이블을 저장할 때 사용됩니다.

RECURSIVE의 여부에 따라 재귀와 비재귀 두가지 방법으로 사용 가능합니다.

 

WITH [RECURSIVE] TABLE명 AS (
    SELECT - # 비반복문. 무조건 필수
    [UNION ALL] # RECURSIVE 사용 시 필수. 다음에 이어붙어야 할 때 사용
    SELECT - 
    [WHERE -] # RECURSIVE 사용 시 필수. 정지 조건 필요할 때 사용
)

 

WITH RECURSIVE 구문

WITH RECURSIVE 구문은 가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용됩니다.

WITH RECURSIVE CTE AS (
    SELECT 1 AS NUM -- 초기값 설정
    UNION ALL
    SELECT NUM + 1
    FROM CTE -- 자기 자신 참조
    WHERE NUM < 12 -- 종료조건
)
SELECT * FROM CTE;

 

위 쿼리의 결과는 아래와 같습니다.

 

 

WITH RECURSIVE 구문을 활용하는 문제

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

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

프로그래머스

 

처음에는 이렇게 풀려고 했으나, 어림도 없습니다.

집계함수의 COUNT는 데이터가 없다면 아예 결과 ROW에 나타나지 않아 IFNULL도 소용이 없습니다.

입양 내역이 없는 시간의 카운트도 0으로 표시해주려면, 0~23까지의 시간을 각각의 행으로 가지고 있는 임시테이블(CTE)을 하나 만들고, 시간별로 동물 입양수를 카운트하는 테이블을 하나 만들어준 후, CTE에 대해 Left Join(순서에 따라서는 Right Join) 걸어주고 IFNULL 처리해주어야합니다.

WITH RECURSIVE CTE AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM CTE
    WHERE HOUR < 23 -- 0시부터 23시까지
),
ANIMAL_COUNT AS (
    SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
)
SELECT CTE.HOUR AS HOUR, IFNULL(ANIMAL_COUNT.COUNT, 0) AS COUNT
FROM ANIMAL_COUNT
RIGHT JOIN CTE ON CTE.HOUR = ANIMAL_COUNT.HOUR
GROUP BY HOUR
ORDER BY HOUR;

 

 

Reference

https://horang98.tistory.com/10

[MySQL] 계층 쿼리 - WITH, WITH RECURSIVE 사용법