[MySQL] RECURSIVE 재귀 쿼리

문제를 풀던 도중 재귀 쿼리를 사용해야 하는 문제가 나왔다.

 

JAVA로는 재귀를 많이 구현해 봤는데, MySQL로 쿼리 짤 때는 사실 재귀를 많이 사용은 안 하는 것 같다.

 

WITH RECURSIVE (재귀)

WITH RECURSIVE TABLE AS(
    SELECT 1 AS i
    
    UNION ALL
    
    SELECT (i+1) AS i
    FROM TABLE
    WHERE i < 3
)

• 재귀 쿼리를 사용하면 반드시 UNION 을 사용해줘야 한다.

 

재귀 쿼리를 만드는 순서

1. WITH RECURSIVE 가상테이블명(= 결과 테이블) 을 지정해준다.

WITH RECURSIVE TABLE AS( ... )

2. 초기 조건을 설정해준다. 

WITH RECURSIVE TABLE AS(
    SELECT 1 AS i
    
    ...
)

 

3. UNION ALL을 사용해서 반복조건과 합쳐준다.

WITH RECURSIVE TABLE AS(
    SELECT 1 AS i
    
    UNION ALL
	
    ...
)

 

4. 반복 조건을 설정해준다.

SELECT N+1 AS N
FROM TABLE
WHERE N < 3 -- break 조건문

WITH RECURSIVE TABLE AS(
    SELECT 1 AS i
    
    UNION ALL
    
    SELECT (i+1) AS i
    FROM TABLE
    WHERE i < 3
)

 

해당 코드에서는 i가 3이 되는 순간 멈춰 i가 2까지 증가하게 된다.

 

테이블 내에서 여러 컬럼을 재귀에 사용할 경우

예를들어 PATIENT 테이블이 하단과 같다고 가정했을 때,

ID	TRANSLATOR_ID	DATE
1	NULL	2017-01-03
2	NULL	2017-01-03
3	1	2017-01-04
4	2	2017-01-04
5	1	2017-01-05
6	3	2017-01-08
7	6	2017-01-09
8	4	2017-01-10
9	8	2017-01-15

 

감기를 4번째로 옮은 사람을 확인하고 싶을 경우

 

1. WITH RECURSIVE LIST(테이블명)에 처음 감기에 걸린 사람(=TRANSLATOR_ID IS NULL)을 지정해 준 뒤, GEN컬럼을 만들어 숫자 1을 부여해준다.

WITH RECURSIVE LIST AS(
    SELECT ID, TRANSLATOR_ID, 1 AS GEN
    FROM PATITIONS
    WHERE TRANSLATOR_ID IS NULL
    
    UNION ALL
    
    ...
)

 

PATIENT 테이블

처음 조건을 지정해준 LIST 테이블

 

2. 반복 조건을 지정해주고, break 할 조건을 지정해준다.

WITH RECURSIVE LIST AS(
    SELECT ID, TRANSLATOR_ID, 1 AS GEN
    FROM PATITIONS
    WHERE TRANSLATOR_ID IS NULL
    
    UNION ALL
    
    SELECT P.ID, P.TRANSLATOR_ID, GEN+1 AS GEN
    FROM PATITIONS P JOIN LIST L ON P.TRANSLATOR_ID = L.ID --break할 조건문
)

PATIENT 테이블

첫번째 재귀가 끝난 LIST 테이블

 

ID 3, 4, 5는 첫번째 재귀 이후 GEN = 2 로 갱신된다. 

해당 테이블을 UNION ALL 한 것이 위의 LIST 테이블!