[MSSQL[ 계층형쿼리,재귀쿼리,트리쿼리 (Feat. with CTE)

아래와 같이 상위품목, 하위품목 의 BOM 이라는 테이블이 있을때,
상위품목 과 하위품목을 비교하여, 레벨 이나 계층형으로 조회를 가능하도록 해주는 방법에 대한 정리입니다.
※ 오라클의 경우에는 START WITH... CONNECT BY PRIOR 구문을 사용한다고 함.

상위품목(CD_ITEM)	하위품목(CD_MATL)
품목1	품목11
품목1	품목12
품목1	품목13
품목11	품목111
품목11	품목112

DECLARE @P_CD_COMPANY NVARCHAR(7) = '1000'
	  , @P_DT_IO NVARCHAR(8) = '20231115'
	  , @P_CD_ITEM NVARCHAR(50) = '05961-69000'
;

WITH PR_BOM_CTE AS (

	-- 앵커 멤버
    SELECT 
		   1 AS LEVEL
         , CD_ITEM
		 , CD_MATL
		 , CONVERT(VARCHAR(500), CD_ITEM + '>' + CD_MATL) AS CD_PATH
		 , CD_COMPANY
    FROM BOM WITH(NOLOCK)
    WHERE CD_COMPANY = @P_CD_COMPANY
	AND CD_ITEM = @P_CD_ITEM
	AND DT_START <= @P_DT_IO

    UNION ALL

	-- 재귀 멤버
    SELECT 
		  (PBC.LEVEL + 1) AS LEVEL
         , PB.CD_ITEM
		 , PB.CD_MATL
		 , CONVERT(VARCHAR(500), PBC.CD_PATH + '>' + PB.CD_ITEM + '>' + PB.CD_MATL) AS CD_PATH
		 , PB.CD_COMPANY
    FROM BOM PB WITH(NOLOCK)
    INNER JOIN PR_BOM_CTE PBC
            ON PBC.CD_COMPANY = PB.CD_COMPANY
			AND PBC.CD_MATL = PB.CD_ITEM

	WHERE PBC.CD_COMPANY = @P_CD_COMPANY
)

앵커멤버의 SELECT부분은 기준이되는 데이터이며, 1번만 실행이되며,
두 번째 SELECT문인 "재귀멤버"부분이 조회 할때 데이터가 있는 경우
계속 실행이 됩니다.

 

---

상세한 설명을 원하시면 하단의 MSSQL 공식문서를 참고하시기 바랍니다.
https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

WITH common_table_expression (Transact-SQL) - SQL Server

 

끝