sql - 对自引用表的递归查询(非分层)

Question

我正在创建一个各种状态图表，其中的数据存储在一个简单的自引用表 (JobPath) 中

JobId - ParentJobId

我正在使用标准的 SQL CTE 来获取数据，该数据运行良好，直到我最终得到以下数据

JobId - ParentId
  1        2
  2        3
  3        4
  4        2

现在，您可以看到工作 4 链接到工作 2，工作 2 转到工作 3，然后再转到工作 4，依此类推。

有什么办法可以告诉我的查询不要提取它已经拥有的数据？

这是我当前的查询

WITH JobPathTemp (JobId, ParentId, Level)
AS
(
-- Anchor member definition
    SELECT j.JobId, jp.ParentJobId, 1 AS Level
    FROM Job AS j
    LEFT OUTER JOIN dbo.JobPath AS jp
        ON j.JobId = jp.JobId
    where j.JobId=1516
    UNION ALL
-- Recursive member definition
    SELECT  j.JobId, jp.ParentJobId, Level + 1
    FROM dbo.Job as j
    INNER JOIN dbo.JobPath AS jp
        ON j.JobId = jp.JobId
    INNER JOIN JobPathTemp AS jpt
        ON jpt.ParentId = jp.JobId
      WHERE jp.ParentJobId <> jpt.JobId 
)

-- 执行 CTE 的语句

SELECT * FROM JobPathTemp

Answer 1

如果您不处理大量条目，以下解决方案可能是合适的。这个想法是为每一行构建完整的“id 路径”，并确保“当前 id”(在递归部分)不在正在处理的路径中:

(出于测试目的，我删除了与工作路径的连接，但基本模式应该相同)

WITH JobPathTemp (JobId, ParentId, Level, id_path)
作为
(
选择作业编号，
parent ，
1 作为级别，
'|' + cast(jobid as varchar(max)) as id_path
从工作
WHERE jobid = 1

联合所有

选择 j.JobId，
j.parentid,
等级 + 1,
jpt.id_path + '|' + cast(j.jobid as varchar(max))
FROM 作业为 j
INNER JOIN JobPathTemp AS jpt ON j.jobid = jpt.parentid
AND charindex('|' + cast(j.jobid as varchar), jpt.id_path) = 0
)
选择 *
来自 JobPathTemp
;