조직도 나타내기 - 재귀적호출

재귀적이란 자기 자신을 호출하는 것을 말한다.

USE sqlDB
GO
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3))
GO

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;

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;

--수정
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 AS [직원부서]
FROM empCTE ORDER BY dept, level