WITH CTE_TABLE을 이용한 쿼리문의 단순화

구문형식

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

<쿼리문>

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

쿼리문에 의한 결과를 "CTE_테이블"이라는 임시 테이블에 담고
하단부의 SELECT  구문을 이용하여 임시 테이블을 가지고 쿼리를 쓸 수 있다.

예제

WITH abc(userid, total)
AS
(SELECT userid, SUM(price * amount)
 FROM buyTbl GROUP BY userid)
SELECT * FROM abc ORDER BY total DESC;

--> WITH abc(열목록) 에서의 열목록과 AS절 이하의  SELECT문의 열 목록이 동일해야 함
--> 중복(다중) CTE도 허용됨

WITH 
  AAA (컬럼들)
     AS (AAA의 쿼리문),
  BBB (컬럼들)
     AS (BBB의 쿼리문),
  CCC (컬럼들)
     AS (CCC의 쿼리문)
SELECT * FROM [AAA 또는 BBB 또는 CCC]

--> BBB  쿼리문에서는 AAA를 사용할 수 있으나 AAA에서는  BBB를 사용할 수 없음
--> 뷰와 다른 점은 해당 쿼리문이 수행되는 세션에서만 생겼다가 사라진다는 점이다.(뷰는 지속가능한 반면)

재귀적 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. <쿼리문1> 실행
   2. <쿼리문2> 실행, 기본값을 +1 증가, SELECT의 결과가 빈것이 아니라면 'CTE_테이블이름' 을 다시 재귀적으로 호출
   3. 계속 2. 번 반복, SELECT 의 결과가 없다면 재귀호출 중지
   4.  외부의 SELECT 문을 실행해서 앞단계의 누적된 결과(UNION ALL) 을 가져옴

예)

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

SELECT emp, manager, department, 0
FROM empTbl
WHERE manager IS NULL -- 상관이 없는 사람이 바로 ROOT(사장)

 UNION ALL

SELECT AA.emp, AA.manaager, 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  -- 결과 1
SELECT replicate('ㄴ', level) + empName AS [직원이름], dept [직원부서]
 FROM empCTE ORDER BY dept, level  -- 결과 2

--> 결과1

	empName	mgrName	Dept	level
1	나사장	NULL	NULL	0
2	이영업	나사장	영업부	1
3	한과장	이영업	영업부	2
4	김재무	나사장	재무부	1
5	김부장	김재무	재무부	2
6	이부장	김재무	재무부	2
7	우대리	이부장	재무부	3
8	지사원	이부장	재무부	3
9	최정보	나사장	정보부	1
10	윤차장	최정보	정보부	2
11	이주임	윤차장	정보부	3

--> 결과2

	직원이름	직원부서
1	나사장	NULL
2	ㄴ이영업	영업부
3	ㄴㄴ한과장	영업부
4	ㄴ김재무	재무부
5	ㄴㄴ김부장	재무부
6	ㄴㄴ이부장	재무부
7	ㄴㄴㄴ우대리	재무부
8	ㄴㄴㄴ지사원	재무부
9	ㄴ최정보	정보부
10	ㄴㄴ윤차장	정보부
11	ㄴㄴㄴ이주임	정보부