데이터베이스 질문과 답변
두개의 테이블에 저장된 데이터를 재귀형식으로 출력하려합니다.
WITH LOG_CTE AS ( SELECT TaskLotLogs.* , TaskLogs.TaskLogID, TaskLogs.TaskID, TaskLogs.TaskType, TaskLogs.PreviousTaskLotLogID, TaskLogs.TaskQuantity, 0 as Level FROM TaskLotLogs Join TaskLogs ON TaskLotLogs.TaskLotLogID = TaskLogs.TaskLotLogID WHERE TaskLotLogs.TaskLotLogID = 1497 UNION ALL SELECT JoinedTaskLotLog.*, LOG_CTE.Level + 1 AS Level FROM ( SELECT TaskLotLogs.*, TaskLogs.TaskLogID, TaskLogs.TaskID, TaskLogs.TaskType, TaskLogs.PreviousTaskLotLogID, TaskLogs.TaskQuantity FROM TaskLotLogs JOIN TaskLogs On TaskLotLogs.TaskLotLogID = TaskLogs.TaskLotLogID ) AS JoinedTaskLotLog JOIN LOG_CTE ON JoinedTaskLotLog.PreviousTaskLotLogID = LOG_CTE.TaskLotLogID ) SELECT * FROM LOG_CTE ORDER BY Level
TaskLotLogs 테이블 데이터가 셀프 참조하는 경우가 있어 무한루프에 빠집니다.
그래서 아래와 JOIN 절 다음에 재귀 탈출 조건을 추가하려했는데...
JOIN LOG_CTE ON JoinedTaskLotLog.PreviousTaskLotLogID = LOG_CTE.TaskLotLogID WHERE JoinedTaskLotLog.TaskLogID not in (select TaskLogID from LOG_CTE)
그런데 MSSQL 재귀쿼리에서는 서브쿼리를 지원하지 않아서...
어떻게 해결해야할지 감이 오질 않네요. 어떤식으로 접근을 하면 되는지 도움좀 부탁드리겠습니다.
by 마농
[2018.04.12 18:41:11]
WITH JoinedTaskLotLog AS
(
SELECT a.*
, b.TaskLogID
, b.TaskID
, b.TaskType
, b.PreviousTaskLotLogID
, b.TaskQuantity
, 0 AS Level
FROM TaskLotLogs a
INNER JOIN TaskLogs b
ON a.TaskLotLogID = b.TaskLotLogID
)
, log_cte AS
(
SELECT a.*
, 0 AS Level
, CAST(CONCAT('-', TaskLotLogID, '-') AS VARCHAR(999)) v_path
FROM JoinedTaskLotLog a
WHERE TaskLotLogID = 1497
UNION ALL
SELECT a.*
, b.Level + 1 AS Level
, CONCAT(b.v_path, a.TaskLotLogID, ',') v_path
FROM JoinedTaskLotLog a
INNER JOIN log_cte b
ON a.PreviousTaskLotLogID = b.TaskLotLogID
WHERE CHARINDEX(CONCAT('-', TaskLotLogID, '-'), b.v_path, 1) = 0
)
SELECT *
FROM log_cte
;
) 버튼을 클릭하여 작성 하시면 됩니다.