# 재귀적 CME SQL 사용례 (in MSSQL)
WITH CODE_MST_CTE (NO, CODE, CODE_NAME, CODE_GROUP, DEPTH, SORT_ORDER, BINARY_SORT)
AS(SELECT
NO,
CODE,
CODE_NAME,
CODE_GROUP,
DEPTH,
SORT_ORDER,
CAST(SORT_ORDER AS VARBINARY(16))
FROM
CODE_MST A
WHERE
DEPTH = 1 AND
CODE = ?
UNION ALL
SELECT
B.NO,
B.CODE,
B.CODE_NAME,
B.CODE_GROUP,
B.DEPTH,
B.SORT_ORDER,
CAST(BINARY_SORT + CAST(B.SORT_ORDER AS BINARY(4)) AS VARBINARY(16))
FROM
CODE_MST B INNER JOIN CODE_MST_CTE A ON A.NO = B.CODE_GROUP
)
SELECT
D.NO,
B.CODE_NAME,
C.CODE_NAME,
D.CODE_NAME,
E.VALNER_GRADE,
E.VALNER_YN,
D.DEPTH,
D.SORT_ORDER,
A.BINARY_SORT
FROM
CODE_MST_CTE A
INNER JOIN CODE_MST B
ON B.CODE_GROUP = A.NO AND B.DEPTH = 2
INNER JOIN CODE_MST C
ON C.CODE_GROUP = B.NO AND C.DEPTH = 3
INNER JOIN CODE_MST D
ON D.CODE_GROUP = C.NO
LEFT OUTER JOIN PCC_JUDG_ITEM E
ON D.NO = E.JUDG_GROUP_CODE
WHERE
B.NO = ?
AND
C.NO = ?
ORDER BY
A.BINARY_SORT
OPTION (MAXRECURSION 4);
댓글 없음:
댓글 쓰기