데이터베이스 질문과 답변

MSSQL 재귀쿼리. 탈출 조건 추가 질문입니다. 1

by 브루스 [SQLServer] 재귀 with cte 서브쿼리 [2018.04.12 17:35:04]

 

두개의 테이블에 저장된 데이터를 재귀형식으로 출력하려합니다.

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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입