출처1 : https://stackoverflow.com/questions/31827817/sql-server-equivalent-of-oracle-connect-by-prior-and-order-siblings-by

출처2(mysql) : https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

어찌 하다보니 트리구조를 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

[MSSQL] WITH 절을 사용한 재귀쿼리_오라클 START WITH CONNECT BY