2016년 2월 15일 월요일

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