MSSQL CTE 재귀 쿼리(2005이상부터 사용가능)

실전 예제이다.

무한 단계에 대한 BOM 을 작성 하여 펼치는 것이다.

일반적으로 Tree구조를 풀어 해치는 방법이라 보면 쉽게 이해 할 수 있다.

Drag and drop 구현 하다가 역시 필요 하더군.

까먹을까봐 적어 둠.

혹 봤으면 댓글좀!

MSDN : http://msdn.microsoft.com/ko-kr/library/ms186243(v=sql.90).aspx

설명(MSDN 참고)

CTE(공통 테이블 식)를 사용하면 자기 자신을 참조하는 재귀적 CTE를 만들 수 있으므로 상당히 유용합니다. 재귀적 CTE는 최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위 집합을 반환하는 CTE입니다.

SQL Server 2005 에서는 재귀적 CTE를 참조는 쿼리를 재귀 쿼리라고 합니다. 재귀 쿼리의 일반적인 용도는 계층적 데이터를 반환하는 것입니다. 예를 들어 직원을 조직도에 표시하는 경우 또는 부모 제품에 하나 이상의 구성 요소가 있고 이러한 구성 요소가 하위 구성 요소를 가지거나 다른 부모의 구성 요소일 수도 있는 제품 구성 정보(BOM) 시나리오에 데이터를 표시하는 경우가 있습니다.

재귀적 CTE는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 내에서 재귀 쿼리를 실행하는 데 필요한 코드를 상당히 단순화할 수 있습니다. 이전 버전의 SQL Server 에서 재귀 쿼리를 실행하려면 일반적으로 임시 테이블, 커서 및 논리를 사용하여 재귀 단계의 흐름을 제어해야 합니다. 공통 테이블 식에 대한 자세한 내용은 공통 테이블 식 사용을 참조하십시오.

기본 구조 (MSDN 참고)

재귀적 CTE 구조에는 앵커 멤버와 재귀 멤버가 적어도 하나씩 포함되어야 합니다. 다음 의사 코드에서는 단일 앵커 멤버와 단일 재귀 멤버가 포함된 간단한 재귀적 CTE의 구성 요소를 보여 줍니다.

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

재귀 실행의 의미 체계는 다음과 같습니다.

CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.

앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듭니다.

Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행합니다.

빈 집합이 반환될 때까지 3단계를 반복합니다.

결과 집합을 반환합니다. 이것은 T0에서 Tn까지의 UNION ALL입니다

재귀적 구조(MSDN 참고)

Transact-SQL의 재귀적 CTE 구조는 다른 프로그래밍 언어의 재귀 루틴과 비슷합니다. 다른 언어의 재귀 루틴은 스칼라 값을 반환하지만 재귀적 CTE는 여러 행을 반환할 수 있습니다.

재귀적 CTE는 다음 세 요소로 구성됩니다.

루틴의 호출

재귀적 CTE의 첫 번째 호출은 UNION ALL, UNION, EXCEPT 또는 INTERSECT 연산자로 조인된 하나 이상의 CTE_query_definitions로 구성됩니다. 이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 합니다.

CTE_query_definitions는 CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주됩니다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 합니다.

루틴의 재귀 호출

재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 CTE_query_definitions가 포함됩니다. 이러한 쿼리 정의를 재귀 멤버라고 합니다. 

종료 확인 

종료 확인은 암시적으로 수행됩니다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지됩니다. 

아래는 실전 예제!

WITH BOMCTE( MMNO_L, PPR_L, MDES_L
, MYNO_L, TPNO_L,LEV, BIUM_L
, NET_L, QTY_L, DT1_L, DT2_L,sort)
AS
(
	SELECT  MMNO, PPR, MDES, MYNO, TPNO, 1 AS LEV , BIUM, NET, QTY, DT1, DT2
	,CONVERT(varchar(MAX),  PPR + '' +  CONVERT(VARCHAR(5), MYNO) ) as sort
	 FROM   WHERE  MMNO=@param	AND  TPNO = -1
	UNION ALL
	SELECT  MMNO, PPR, MDES, MYNO, TPNO, LEV + 1 AS LEV, BIUM, NET, QTY, DT1, DT2
	,CONVERT(varchar(MAX), RTRIM(sort) + '|' + LP. PPR + '' + CONVERT(VARCHAR(5),LP. MYNO)) as sort
	 FROM   LP , BOMCET 
	 WHERE LP. TPNO = BOMCET. MYNO_L
)
SELECT * FROM BOMCTE
order by sort

추가 예제는 MSDN 이나 구글링!