쿼리를 짜다보면 트리 구조 형태로 데이터를 만들어야 하는 경우가 종종있다. 이를 경우 MSSQL에서 사용할 수 있는 방법이 있다. 바로 재귀 쿼리 이다. 재귀적 CTE의 기본 형식은 다음과 같다.
WITH CTE_RECURSIVE(column_name[,...n]) AS
(
<쿼리문1: SELECT * FROM TABLE_A // Anchor member
UNION ALL
<쿼리문2: SELECT * FROM TABLE_A JOIN CTE_테이블 이름 // Recursive member
)
-- Statement using the CTE --
SELECT * FROM CTE_RECURSIVE
재귀 실행의 의미 체계는 다음과 같습니다.
1. CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.
2. 앵커 멤버를 실행(쿼리문1)하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듭니다.
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버(쿼리문2)를 실행합니다.
4. 빈 집합이 반환될 때까지 3단계를 반복합니다.
5. 결과 집합을 반환합니다. 이것은 T0에서 Tn까지의 UNION ALL입니다.
지금부터 예제를 통해 적용해보자.
아래는 트리구조로 만든 임시 테이블이다.
WITH TEST(UPPER_CODE,CODE,NAME) AS (
SELECT NULL,10, 'LEVEL1'
UNION ALL
SELECT '10','1001','LEVEL2'
UNION ALL
SELECT NULL,20, 'LEVEL1'
UNION ALL
SELECT '20','2001','LEVEL2'
UNION ALL
SELECT '2001','2001001','LEVLE3'
)
SELECT * FROM TEST<실행결과>
UPPER_CODE CODE NAME
NULL 10 CODE10
10 1001 CODE1001
NULL 20 CODE20
20 2001 CODE2001
2001 2001001 CODE2001001
CODE 2001001을 보자. UPPER_CODE 가 2001이다. 그럼 다시 CODE가 2001인 데이터는 UPPDER CODE가 20이고 ITEM CODE=20인 데이터는 UPPER_CODE가 NULL이다. 즉, 레벨 구조가 아래와 같다.
20 L 2001 L2001001
그럼 재귀 쿼리를 이용하여 각 CODE의 레벨을 지정 해보자. (레벨 1: 1, 레벨2: 2, 레벨3: 3) 각 레벨에 숫자를 부여하는 쿼리와 실행 결과는 아래를 참고하기 바란다.
실행 결과는 아래와 같다
그럼 좀더 응용해보자. 총 레벨이 3단계 까지 있으므로 각 코드의 상위 레벨들의 CODE와 NAME을 한 ROW에 모두 나오도록 QUERY를 작성해보자.
예를 들어, 20 의 경우 L 2001 L2001001
CODE1 CODE1_NAME CODE2 CODE2_NAME CODE3 CODE3_NAME LEVEL
20 ITEM20 2001 ITEM2001 2001001 ITEM2001001 3
이렇게 출력 될 수 있도록 말이다. 그럼 우선, 위 쿼리 실행 결과를 뷰 테이블로 만들고 다시 재귀 쿼리를 이용해야 한다. Microsoft SQL Server Management Studio에서 View 테이블을 직접 만들어도 되지만, 여기서 공부하는 용으로 정리하는 것이니 WITH AS 을 이용하여 위 데이터를 가지는 임시 vm_table을 만들어 사용했다.
WITH VM_CTE(UPPER_CODE,CODE,NAME,LEVEL) AS (
SELECT NULL, '10', 'ITEM10', 1
UNION ALL
SELECT NULL, '20' ,'ITEM20', 1
UNION ALL
SELECT '20', '2001' ,'ITEM12001', 2
UNION ALL
SELECT '2001', '2001001', 'ITEM2001001', 3
UNION ALL
SELECT '10', '1001', 'ITEM101001', 2
UNION ALL
SELECT '1001', '1001001', 'ITEM101001', 3
)
SELECT * FROM VM_CTE
이 뷰테이블 VM_CTE를 이용하여 작성한 쿼리와 실행 결과를 아래에 정리해두었다. 참고하기 바란다.
WITH VM_CTE(UPPER_CODE,CODE,NAME,LEVEL) AS (
SELECT NULL, '10', 'ITEM10', 1
UNION ALL
SELECT NULL, '20' ,'ITEM20', 1
UNION ALL
SELECT '20', '2001' ,'ITEM2001', 2
UNION ALL
SELECT '2001', '2001001', 'ITEM2001001', 3
UNION ALL
SELECT '10', '1001', 'ITEM1001', 2
UNION ALL
SELECT '1001', '1001001', 'ITEM1001001', 3
) ,CTE_RESURSIVE AS (
SELECT CODE, CODE CODE1,
CASE WHEN 1=2 THEN CODE ELSE NULL END CODE2,
CASE WHEN 1=2 THEN CODE ELSE NULL END CODE3,
NAME NAME1,
CASE WHEN 1=2 THEN NAME ELSE NULL END NAME2,
CASE WHEN 1=2 THEN NAME ELSE NULL END NAME3,
LEVEL
FROM VM_CTE WHERE UPPER_CODE IS NULL
UNION ALL
SELECT SC.CODE, CC.CODE1 CODE1,
CASE WHEN SC.LEVEL = 1 THEN NULL
WHEN SC.LEVEL = 2 THEN SC.CODE
WHEN SC.LEVEL = 3 THEN CC.CODE2 END CODE2,
CASE WHEN SC.LEVEL = 1 THEN NULL
WHEN SC.LEVEL = 2 THEN NULL
WHEN SC.LEVEL = 3 THEN SC.CODE END CODE3 ,
CC.NAME1 NAME1,
CASE WHEN SC.LEVEL = 1 THEN NULL
WHEN SC.LEVEL = 2 THEN SC.NAME
WHEN SC.LEVEL = 3 THEN CC.NAME2 END NAME2,
CASE WHEN SC.LEVEL = 1 THEN NULL
WHEN SC.LEVEL = 2 THEN NULL
WHEN SC.LEVEL = 3 THEN SC.NAME END NAME3 ,
SC.LEVEL
FROM VM_CTE SC INNER JOIN CTE_RESURSIVE CC ON SC.UPPER_CODE=CC.CODE
WHERE UPPER_CODE IS NOT NULL ) SELECT * FROM CTE_RESURSIVE
WITH VM_CTE(UPPER_CODE,CODE,NAME,LEVEL) AS (
SELECT NULL, '10', 'ITEM10', 1
UNION ALL
SELECT NULL, '20' ,'ITEM20', 1
UNION ALL
SELECT '20', '2001' ,'ITEM2001', 2
UNION ALL
SELECT '2001', '2001001', 'ITEM2001001', 3
UNION ALL
SELECT '10', '1001', 'ITEM1001', 2
UNION ALL
SELECT '1001', '1001001', 'ITEM1001001', 3
) ,CTE_RESURSIVE AS (
SELECT CODE,
CODE CODE1,
CASE WHEN 1=2 THEN CODE
ELSE NULL END CODE2,
CASE WHEN 1=2 THEN CODE
ELSE NULL END CODE3,
NAME NAME1,
CASE WHEN 1=2 THEN NAME
ELSE NULL END NAME2,
CASE WHEN 1=2 THEN NAME
ELSE NULL END NAME3,
LEVEL
FROM VM_CTE
WHERE UPPER_CODE IS NULL
UNION ALL
SELECT SC.CODE,
CC.CODE1 CODE1,
CASE WHEN SC.LEVEL=1 THEN NULL
WHEN SC.LEVEL=2 THEN SC.CODE
WHEN SC.LEVEL=3 THEN CC.CODE2
END CODE2,
CASE WHEN SC.LEVEL= 1 THEN NULL
WHEN SC.LEVEL = 2 THEN NULL
WHEN SC.LEVEL = 3 THEN SC.CODE
END CODE3 ,
CC.NAME1 NAME1,
CASE WHEN SC.LEVEL=1 THEN NULL
WHEN SC.LEVEL=2 THEN SC.NAME
WHEN SC.LEVEL=3 THEN CC.NAME2
END NAME2,
CASE WHEN SC.LEVEL= 1 THEN NULL
WHEN SC.LEVEL = 2 THEN NULL
WHEN SC.LEVEL = 3 THEN SC.NAME
END NAME3 ,
SC.LEVEL
FROM VM_CTE SC
INNER JOIN CTE_RESURSIVE CC ON SC.UPPER_CODE=CC.CODE
WHERE UPPER_CODE IS NOT NULL
)
SELECT *
FROM CTE_RESURSIVE<<실행결과 >>
위 쿼리를 잠깐 살펴보자.
재귀 쿼리는 Anchor 멤버를 먼저 실행후 ,T0을 만든다고 했다. 그래서 위 쿼리의 Anchor멤버를 실행하게 되면 T0가 생성이 된다.
T0
| CODE | CODE1 | CODE2 | CODE3 | NAME1 | NAME2 | NAME3 | LEVEL |
| 10 | 10 | NULL | NULL | ITEM10 | NULL | NULL | 1 |
| 20 | 20 | NULL | NULL | ITEM20 | NULL | NULL | 1 |
T1
T0가 입력으로 사용되어 Recursive멤버가 실행되게 된다. 위 쿼리에서 Recursive 멤버인 아래 쿼리가 실행이 되는 것이다.
SELECT SC.CODE,
......생략......
SC.LEVEL
FROM VM_CTE SC
INNER JOIN CTE_RESURSIVE CC ON SC.UPPER_CODE=CC.CODE
WHERE UPPER_CODE IS NOT NULL
그럼 T0은 CC 로 Alias된 부분이 되고 SC로 ALIAS된 Table과 Inner Join하게 되는 것이다. 즉, T0의 CODE가 SC의 UPPER_CODE인 Record를 찾게된다. 그 결과가 T1이 된다.
T2
위에서 생성된 T1이 다시 입력으로 들어가서 SC로 Alias된 Table과 Inner Join하게 되고 그 결과로 T2가 만들어진다.
T3
위에서 생성된 T2가 다시 입력으로 들어가서 SC로 Alias된 Table과 Inner Join하게 되고 그 결과로 T3가 만들어진다.
그런데 그 결과 Record가 존재 하지 않는다. 그래서 T3는 만들어 지지 않고 재귀 호출은 여기서 종료 된다.
T1,T2,T3 UNION ALL
재귀 호출이 종료된 후 지금 까지 생성된 T0,T1,T2 가 UNION ALL되어 출력되게 된다.
처음 재귀 쿼리를 접했을때 이 내용을 이해하기 까지 시간이 오래 걸렸다. 나 처럼 재귀쿼리 첨 접하는 사람에게 조금이나마 이해하는데 도움이 되었으면 좋겠다...
'MSSQL' 카테고리의 다른 글
| db링크 확인 하는 법 (0) | 2020.03.27 |
|---|---|
| bulk insert (0) | 2020.03.26 |
| STRING ORDER BY 가 안될때 (2) | 2020.03.20 |
| 숫자+문자를 포함한 데이터 order by 가 안되는 경우 (0) | 2020.02.04 |
| 여러 행의 컬럼값을 한줄로 나타내기 (0) | 2019.12.30 |