Common Table Expression

Common Table Expression

기본 구조

WITH [CTE의 이름] ( [CTE에서 반환하는 열] )

AS (

 Table View

)

SELECT *FROM [CTE의 이름];

주의 사항

1. WITH 전에 GO 혹은 세미콜론(;)을 사용해야 함
2. [CTE에서 반환하는 열]은 AS 안의 Table View의 반환값과 같아야 함
3. AS 안에서는 내부 조인만 가능하다.

예제

이 예제에서는 CTE를 사용하여 반복+계층 질의문을 반환하도록 한다.

1. 예제 테이블 형식

[부서 ID], [부서 명] , [상위 부서 ID], [사용자 명]

2. 예제 테이블 기본 값

부서 ID	부서 명	상위 부서 ID	사용자 명
Dev	개발부	NULL	김개발
Ope	영업부	NULL	이영업
Dev1	개발1팀	Dev	김일팀
Dev2	개발2팀	Dev	김이팀
Dev1-1	개발1팀1조	Dev1	김일조
Ope1	영업1팀	Ope	이일팀
Ope2	영업2팀	Ope	이이팀

3. 질의문 작성

3-1. QUERY TEMPLATE

WITH CTE_DEPT ( [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명] )

AS (

 Table View

)

SELECT *FROM CTE_DEPT

3-2. 기준이 되는 테이블 (상위부서가 없는 행)

WITH CTE_DEPT ( [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명] )
AS (
 -- Table View
SELECT [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명] 
FROM DEPT_TABLE 
WHERE [상위부서 ID] IS NULL 
)
SELECT *FROM CTE_DEPT

부서 ID	부서 명	상위 부서 ID	사용자 명
Dev	개발부	NULL	김개발
Ope	영업부	NULL	이영업

3-3.  반복+계층을 위한 UNION ALL 작성

(기본 테이블의 [상위 부서 ID]와 CTE테이블의 [부서 ID]가 같은 것을 가져온다는 것은
"3-1"에서 가져온 기준이 되는 테이블 외의 것을 가져오는 것을 의미함)
WITH CTE_DEPT ( [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명] )

AS (

 -- Table View

SELECT [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명]

FROM DEPT_TABLE

WHERE [상위부서 ID] IS NULL

UNION ALL 

SELECT A.[부서 ID], A.[부서 명] , A.[상위 부서 ID], A.[사용자 명]

FROM DEPT_TABLE A 

    INNER JOIN CTE_DEPT B ON A.[상위부서 ID] = B.[부서 ID]

)

SELECT *FROM CTE_DEPT

부서 ID	부서 명	상위 부서 ID	사용자 명
Dev	개발부	NULL	김개발
Ope	영업부	NULL	이영업
Dev1	개발1팀	Dev	김일팀
Dev2	개발2팀	Dev	김이팀
Dev1-1	개발1팀1조	Dev1	김일조
Ope1	영업1팀	Ope	이일팀
Ope2	영업2팀	Ope	이이팀

3-4. 계층을 보여주는 LEVEL 함수 사용 및 반복 확인 열 추가

WITH CTE_DEPT ( [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명], LEVEL )

AS (

 -- Table View

SELECT [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명], 0

FROM DEPT_TABLE

WHERE [상위부서 ID] IS NULL

UNION ALL

SELECT A.[부서 ID], A.[부서 명] , A.[상위 부서 ID], A.[사용자 명], B.LEVEL+1

FROM DEPT_TABLE A 

    INNER JOIN CTE_DEPT B ON A.[상위부서 ID] = B.[부서 ID]

)

SELECT [부서 ID], REPLICATE('ㄴ',LEVEL)+ [부서 명] , [상위 부서 ID], [사용자 명], LEVEL

FROM CTE_DEPT

부서 ID	부서 명	상위 부서 ID	사용자 명	LEVEL
Dev	개발부	NULL	김개발	0
Ope	영업부	NULL	이영업	0
Dev1	ㄴ개발1팀	Dev	김일팀	1
Dev2	ㄴ개발2팀	Dev	김이팀	1
Dev1-1	ㄴㄴ개발1팀1조	Dev1	김일조	2
Ope1	ㄴ영업1팀	Ope	이일팀	1
Ope2	ㄴ영업2팀	Ope	이이팀	1

3-5. 정렬 

WITH CTE_DEPT ( [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명], LEVEL )

AS (

 -- Table View

SELECT [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명], 0

FROM DEPT_TABLE

WHERE [상위부서 ID] IS NULL

UNION ALL

SELECT A.[부서 ID], A.[부서 명] , A.[상위 부서 ID], A.[사용자 명], B.LEVEL+1

FROM DEPT_TABLE A 

    INNER JOIN CTE_DEPT B ON A.[상위부서 ID] = B.[부서 ID]

)

SELECT [부서 ID], REPLICATE('ㄴ',LEVEL)+ [부서 명] , [상위 부서 ID], [사용자 명], LEVEL

FROM CTE_DEPT

ORDER BY [부서 ID] , LEVEL

/* 부서 ID가 숫자일 경우 */

-- ORDER BY ISNULL([상위부서 ID], [부서 ID]), LEVEL

부서 ID	부서 명	상위 부서 ID	사용자 명	LEVEL
Dev	개발부	NULL	김개발	0
Dev1	ㄴ개발1팀	Dev	김일팀	1
Dev2	ㄴ개발2팀	Dev	김이팀	1
Dev1-1	ㄴㄴ개발1팀1조	Dev1	김일조	2
Ope	영업부	NULL	이영업	0
Ope1	ㄴ영업1팀	Ope	이일팀	1
Ope2	ㄴ영업2팀	Ope	이이팀	1

3-6. 최상위 부모 가져오기

WITH CTE_DEPT ( [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명], LEVEL, TOPID)

AS (

 -- Table View

SELECT [부서 ID], [부서 명] , [상위 부서 ID], [사용자 명], 0, [부서 ID] AS TOPID

FROM DEPT_TABLE

WHERE [상위부서 ID] IS NULL

UNION ALL

SELECT A.[부서 ID], A.[부서 명] , A.[상위 부서 ID], A.[사용자 명], B.LEVEL+1, B.TOPID

FROM DEPT_TABLE A 

    INNER JOIN CTE_DEPT B ON A.[상위부서 ID] = B.[부서 ID]

)

SELECT [부서 ID], REPLICATE('ㄴ',LEVEL)+ [부서 명] , [상위 부서 ID], [사용자 명], LEVEL, TOPID

FROM CTE_DEPT
ORDER BY [부서 ID], LEVEL

부서 ID	부서 명	상위 부서 ID	사용자 명	TOPID
Dev	개발부	NULL	김개발	Dev
Dev1	개발1팀	Dev	김일팀	Dev
Dev2	개발2팀	Dev	김이팀	Dev
Dev1-1	개발1팀1조	Dev1	김일조	Dev
Ope	영업부	NULL	이영업	Ope
Ope1	영업1팀	Ope	이일팀	Ope
Ope2	영업2팀	Ope	이이팀	Ope