/*******************************************************************************************
-- Title : [2k5] (비)재귀적 CTE 셈플과 BOM 구현 및 들여쓰기 예
-- Reference : hanbitbook.co.kr, BOL
-- Key word : cte 재귀 비재귀 들여쓰기 레벨 level bom
*******************************************************************************************/
/*
** 비재귀적 CTE
*/
USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO
/*
** 재귀적 CTE
*/
USE tempdb;
GO
-- 테스트 테이블/데이터 생성
IF object_id('empTbl') IS NOT NULL
DROP TABLE empTbl;
GO
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3));
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('이주임','윤차장','정보부')
-- 테이블 확인
SELECT * FROM empTbl;
-- BOM 쿼리 LEVEL 구현
WITH empCTE(empName, mgrName, dept, level)
AS
( -- 앵커 멤버(Anchor Member)
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 상관이 없는 사람이 바로 사장
UNION ALL
-- 재귀 멤버(Recursive Member)
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;
-- BOM 쿼리 들여쓰기 구현 1
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;
-- BOM 쿼리 들여쓰기 구현 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;