반응형
/************************************************************************************************
-- Title : [PGS9.2] Impementation CTE or BOM
-- Reference : dbrang.tistory.com
-- Key word : postgresql cte bom recursive
************************************************************************************************/
-- Title : [PGS9.2] Impementation CTE or BOM
-- Reference : dbrang.tistory.com
-- Key word : postgresql cte bom recursive
************************************************************************************************/
-- initialization
DROP TABLE empTbl;
-- createing AND inserting data
CREATE TABLE empTbl
( emp CHAR(3)
, manager CHAR(3)
, department CHAR(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('이주임','윤차장','정보부');
-- confirming data
SELECT * FROM empTbl;
-- implementation of BOM LEVEL
WITH RECURSIVE 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;
-- implementation of BOM Level AND Indentation.1
WITH RECURSIVE 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)*/'L' || empName AS "직원이름", dept AS "직원부서"
FROM empCTE
ORDER BY dept, level;
-- implementation of BOM Level AND Indentation.2
WITH RECURSIVE 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)*/'L' || empName AS "직원이름", dept "직원부서"
FROM empCTE
ORDER BY dept, level;
/* WHEN implement making a identation,
don't know how TO replicate char like SQL Server replicate function*/
반응형