어찌 하다보니 트리구조를 DB로 만들고 싶었다.
이래저래 찾다보니 관련 내용이 있더라.
우선 테이블 구조는 아래와 비슷해야 편하다.
이런식의 구조로 되어 있을 때 아래와 같은 쿼리로 데이터를 뽑아볼 수 있다.
아래 쿼리는 거의 비슷한데 다른 점은 WITH 절에 첫 번째 쿼리문의 '기준' 부분과
WITH 절 두 번째 쿼리의 조인 부분이 약간 다르다.
-- 전체
;WITH ORG_CTE(ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, level)
AS
(
SELECT ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, 0
FROM ORG_TABLE
WHERE ORG_ID = 'ROOT' -- 기준(최상위)
UNION ALL
SELECT AA.ORG_ID, AA.ORG_NAME, AA.DISP_PRIORITY, AA.ORG_PID, AA.FULL_PATH_ID, AA.FULL_PATH_NAME, BB.level+1
FROM ORG_TABLE AS AA
INNER JOIN ORG_CTE AS BB ON AA.ORG_PID = BB.ORG_ID
)
SELECT *
FROM ORG_CTE
ORDER BY FULL_PATH_ID , LEVEL ASC
-- 하향식(본인 직계 자식)
;WITH ORG_CTE(ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, level)
AS
(
SELECT ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, 0
FROM ORG_TABLE
WHERE ORG_ID = 'D@D@00004' -- 기준
UNION ALL
SELECT AA.ORG_ID, AA.ORG_NAME, AA.DISP_PRIORITY, AA.ORG_PID, AA.FULL_PATH_ID, AA.FULL_PATH_NAME, BB.level+1
FROM ORG_TABLE AS AA
INNER JOIN ORG_CTE AS BB ON AA.ORG_PID = BB.ORG_ID
)
SELECT *
FROM ORG_CTE
ORDER BY FULL_PATH_ID , LEVEL ASC
-- 상향식(본인 직계 조상)
;WITH ORG_CTE(ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, level)
AS
(
SELECT ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, 0
FROM ORG_TABLE
WHERE ORG_ID = 'D@D@00004' -- 기준
UNION ALL
SELECT AA.ORG_ID, AA.ORG_NAME, AA.DISP_PRIORITY, AA.ORG_PID, AA.FULL_PATH_ID, AA.FULL_PATH_NAME, BB.level+1
FROM ORG_TABLE AS AA
INNER JOIN ORG_CTE AS BB ON AA.ORG_ID = BB.ORG_PID
)
SELECT *
FROM ORG_CTE
ORDER BY FULL_PATH_ID ASC, LEVEL DESC
-- 상향식 부모트리 전체(본인 항렬보다 조상인 전체)
;WITH ORG_CTE(ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, level)
AS
(
SELECT ORG_ID, ORG_NAME, DISP_PRIORITY, ORG_PID, FULL_PATH_ID, FULL_PATH_NAME, 0
FROM ORG_TABLE
WHERE ORG_ID = 'D@D@00004' -- 기준
UNION ALL
SELECT AA.ORG_ID, AA.ORG_NAME, AA.DISP_PRIORITY, AA.ORG_PID, AA.FULL_PATH_ID, AA.FULL_PATH_NAME, BB.level+1
FROM ORG_TABLE AS AA
INNER JOIN ORG_CTE AS BB ON AA.ORG_ID = BB.ORG_PID
)
SELECT B.*, A.LEVEL
FROM ORG_CTE AS A
LEFT JOIN ORG_TABLE AS B ON A.ORG_PID LIKE B.ORG_PID
ORDER BY LEVEL DESC, DISP_PRIORITY ASC
