[MSSQL] WITH / CTE

WITH 절은 CTE(Common Table Expression)를 표현하는 구문이다. 

CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며, 

더 간결한 식으로 보여지는 장점이 있다.

CTE는 ANSI-SQL99 표준에서 나온 것이다. 기존의 SQL은 대부분 ANSI-SQL92가 기준으로 하지만

최근의 DBMS는 대게 ANSI-SQL99와 호환이 되므로 다른 DBMS에서도 SQL Server와 같거나 비슷한 방식으로 응용된다.

CTE는 비재귀적(Non-Recursive) CTE와 재귀적(Recursive) CTE 두 가지가 있다.

(1) 비재귀적 CTE

​비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE이다.

단순한 형태이며, 복잡한 쿼리 문장을 단순화시키는 데 적합하게 사용될 수 있다.

비재귀적 CTE 형식

WITH CTE_테이블이름(열이름)

AS

(

<쿼리문>

)

SELECT 열이름 FROM CTE_테이블이름 ;

 

예시 ) 

총 구매액이 많은 사용자 순서로 정렬하자

WITH abc(userID, total)

AS

( SELECT userID, SUM(price*amount)

FROM buyTbl GROUP BY userID )

SELECT * FROM abc ORDER BY total DESC ;

CTE는 제일 아래 행의 SELECT 문만 보면 된다.

그리고 제일 아래의 'FROM abc'에서 abc는 실존하는 테이블이 아니라, 바로 위에서 WITH 구문으로 만든

SELECT 결과이다. 단 여기서 'AS (SELECT …'에서 조회하는 열과 'WITH abc (…' 와는 개수가 일치해야 한다.

 

 

예시) DB명:sqlDB / 테이블명:userTbl

각 지역별로 큰 키를 1명씩 뽑은 후에, 그 사람들 키의 평균을 구하자.

한꺼번에 생각하지 말고, 한 단계씩 분할하여 생각해 보자.

1단계 → '각 지역별로 가장 큰 키'를 뽑는 쿼리

SELECT addr, MAX(height) FROM userTbl GROUP BY addr

2단계 → 앞의 쿼리를 WITH 구문으로 묶는다.

WITH cte_테이블이름(addr, maxHeight)

AS

(SELECT addr, MAX(height) FROM userTbl GROUP BY addr)

3단계 → 키의 평균을 구하는 쿼리를 작성한다.

SELECT AVG(키) FROM cte_테이블 이름

4단계 → 2단계와 3단계의 쿼리를 합친다. 키의 평균을 실수로 만들려고 키에다 1.0을 곱해서 실수로 변환했다.

WITH cte_userTbl(addr, maxHeight)

AS

(SELECT addr, MAX(height) FROM userTbl GROUP BY addr)

SELECT AVG(maxHeight*1.0) AS [각 지역별 최고키의 평균] FROM cte_userTbl ;

(2) 재귀적 CTE 

 

재귀적이라는 의미는 자기 자신을 반복적으로 호출한다는 의미를 내포한다.

 

직원 이름(EMP) - 기본키	상관 이름(MANAGER)	부서(DEPARTMENT)
나사장	없음 (NULL)	없음 (NULL)
김재무	나사장	재무부
김부장	김재무	재무부
이부장	김재무	재무부
우대리	이부장	재무부
지사원	이부장	재무부
이영업	나사장	영업부
한과장	이영업	영업부
최정보	나사장	정보부
윤차장	최정보	정보부
이주임	윤차장	정보부

 

 

이것을 테이블로 만들어 보자.

지금은 데이터가 몇개 없고, 테이블을 봐도 상관과 부하 직원의 관계가 보일 수 있지만, 실무에서 많은

직원들이 앞의 테이블만 가지고는 도저히 누가 누구의 상관인지를 파악하기 어렵다.

특히, 사원번호 등이 코드로 되어 있다면 더욱 그러할 것이다.

 

 

재귀적 CTE의 기본 형식은 다음과 같다.

 

WITH CTE_테이블이름(열이름)

AS

(

<쿼리문1 : SELECT * FROM 테이블A >

UNION ALL

<쿼리문2 : SELECT * FROM 테이블A JOIN CTE_테이블이름>

)

SELECT * FROM CTE_테이블이름 ;

 

위의 구문에서 <쿼리문1>을 앵커 멤버(Anchor Member:AM)이라고 부르고, 

                    <쿼리문2>를 재귀멤버(Recursive Member:RM)라고 지칭한다.

 

 

① <쿼리문1>을 실행한다. 이것이 루틴의 최초 호출에 해당한다. 그리고 기본값은 0으로 초기화된다.

② <쿼리문2>를 실행한다. 기본값을 기본값+1로 증가시킨다. 그런데 SELECT의 결과가 빈것이 아니라면,

    'CTE_테이블이름'을 다시 재귀적으로 호출한다

③ 계속 ②번을 반복한다. 단 SELECT의 결과가 아무것도 없다면 재귀적인 호출이 중단된다.

④ 외부의 SELECT 문을 실행해서 앞 단계에서의 누적된 결과(UNION ALL)를 가져온다.

 

 

0. 우선 앞의 테이블을 정의하고 데이터를 입력하자

​

0-1 테이블을 정의해 보자

​CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3)) ;

GO

 

0-2 표와 같은 데이터를 입력해 보자

INSERT INTO empTbl VALUES ('나사장',NULL,NULL);

INSERT INTO empTbl VALUES ('김재무','나사장','재무부');

INSERT INTO empTbl VALUES ('김부장','김재무','재무부');

INSERT INTO empTbl VALUES ('이부장','김재무','재무부');

INSERT INTO empTbl VALUES ('우대리','이부장','재무부');

INSERT INTO empTbl VALUES ('지사원','이부장','재무부');

INSERT INTO empTbl VALUES ('이영업','나사장','영업부');

INSERT INTO empTbl VALUES ('한과장','이영업','영업부');

INSERT INTO empTbl VALUES ('최정보','나사장','정보부');

INSERT INTO empTbl VALUES ('윤차장','최정보','정보부');

INSERT INTO empTbl VALUES ('이주임','윤차장','정보부');

 

 

1. 재귀적 CTE의 구문 형식에 맞춰서 쿼리문을 만들어 보자.

아직 조인(JOIN)을 배우지 않아서 <쿼리문2> 부분이 좀 어렵겠지만, 형식대로 empTbl과 empCTE를 조인하는 방식이다.

 

WITH empCTE(empName, mgrName, dept, level)

AS

(

SELECT emp, manager, department , 0

FROM empTbl

WHERE manager IS NULL -- 상관이 없는 사람이 바로 사장

UNION ALL

SELECT AA.emp, AA.manager, AA.department, BB.level+1

FROM empTbl AS AA INNER JOIN empCTE AS BB

ON AA.manager = BB.empName

)

SELECT * FROM empCTE ORDER BY dept, level ;

 

AA, BB는 각 테이블의 별칭을 붙여준 것이다. 즉, AA.emp는 empTbl.emp(empTbl 테이블의 emp열)과 같은 의미이다.

2. 쿼리문을 약간 수정해서 조금 더 보기쉽게 만들어 보자

​WITH empCTE(empName, mgrName, dept, level)

AS

(

SELECT emp, manager, department , 0

FROM empTbl

WHERE manager IS NULL -- 사장

UNION ALL

SELECT AA.emp, AA.manager, AA.department, BB.level+1

FROM empTbl AS AA INNER JOIN empCTE AS BB

ON AA.manager = BB.empName

)

SELECT replicate(' ㄴ',level) + empName AS [직원이름], dept [직원부서]

FROM empCTE ORDER BY dept, level

replicate(문자, 개수) 함수는 해당 문자를 개수만큼 반복하는 함수이다.

그러므로 레벨에 따라서 'ㄴ' 문자를 출력함으로써 트리 구조 형태로 보여지는 효과를 줬다.

3. 이번에는 사원급을 제외한 부장/차장/과장급까지만 출력해 보자. 레벨 2이므로 간단히

'WHERE level <2' 만 <쿼리문2> 부분에 추가해 주면 된다.

​WITH empCTE(empName, mgrName, dept, level)

AS

(

SELECT emp, manager, department , 0

FROM empTbl

WHERE manager IS NULL -- 사장

UNION ALL

SELECT AA.emp, AA.manager, AA.department, BB.level+1

FROM empTbl AS AA INNER JOIN empCTE AS BB

ON AA.manager = BB.empName

WHERE level < 2

)

SELECT replicate(' ㄴ',level) + empName AS [직원이름], dept [직원부서]

FROM empCTE ORDER BY dept, level