Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환

 

제목	Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환
작성자	DEV.NAM( http://blog.naver.com/kilnam50)
작성일	2007-10-29
배포	public (단, 출처는 꼬~~~옥 밝혀 주세요)
출처	MSDN

SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, LEVEL AS LEV
  FROM BPM_TEMPLETE
 WHERE GROUP_CODE=2
CONNECT BY PRIOR TASK_CODE = UPPER_TASK_CODE
START WITH UPPER_TASK_CODE = 0
order by order_desc

 

위와 같은 방식으로 출력하기 위해서는 CTE와 집계함수를 이용하면 된다.

처음 CTE의 구조를 몰랐을 때는 왜 이렇게 나오는 몰라서 참 고민을 많이 했었다.

하지만 구조를 이해하고 나면 정말 간편하게 출력할 수 있게 되었다.

처음으로 CTE의 기본 문법만 사용하면 아래와 같이 쿼리를 작성할 수 있다.

WITH BPM_CTE(GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_ORDER, TASK_NAME, TASK_DESC, TASK_ROLL, ORDER_DESC, LEVEL)
AS
(
    SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_ORDER, TASK_NAME, TASK_DESC, TASK_ROLL, ORDER_DESC, 1 AS LEVEL
 FROM BPM_TEMPLETE T
 WHERE GROUP_CODE=2

    UNION ALL
   SELECT C.GROUP_CODE, C.TASK_CODE, C.UPPER_TASK_CODE, C.TASK_ORDER, C.TASK_NAME, C.TASK_DESC, C.TASK_ROLL, C.ORDER_DESC, LEVEL+1
  FROM BPM_TEMPLETE C
 INNER JOIN BPM_CTE P
 ON C.UPPER_TASK_CODE = P.TASK_CODE
 WHERE C.GROUP_CODE=2
)
SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_ORDER, TASK_NAME, TASK_DESC, TASK_ROLL, ORDER_DESC, LEVEL
FROM BPM_CTE
WHERE GROUP_CODE=2

ORDER BY ORDER_DESC ASC

결과는 위와같이 같은 정보에 LEVEL이 중복되어서 출력된다.

이와같이 나온 이유는 UNION ALL 이 일반적으로 적용되는 방식과 달라서 헷갈릴 것이다.

이유는

부모 ROW에 자식 ROW가 LEVEL이 증가하면서 쌓이기 때문에 위와같은 결과가 나올 수 밖에 없다.

그래서 집계함수를 사용하여 아래와 같이 쿼리를 작성하였다.

WITH BPM_CTE(GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, LEVEL)
AS
(
    SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, 1 AS LEVEL
 FROM BPM_TEMPLETE T
 WHERE GROUP_CODE=2

    UNION ALL
   SELECT C.GROUP_CODE, C.TASK_CODE, C.UPPER_TASK_CODE, C.TASK_NAME, C.ORDER_DESC, LEVEL+1
  FROM BPM_TEMPLETE C
 INNER JOIN BPM_CTE P
 ON C.UPPER_TASK_CODE = P.TASK_CODE
 WHERE C.GROUP_CODE=2
)
SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, MAX(LEVEL) AS LEVEL
FROM BPM_CTE
WHERE GROUP_CODE=2
GROUP BY GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC

ORDER BY ORDER_DESC ASC

결과는 아래와 같이 깔끔하게 출력된다.

계층도를 뽑을 때 상당히 유용하게 사용되니 필요하신 분은 퍼가시고 Comment도 남겨주세요

[출처] Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환 |작성자 필립박