检索路径的公用表表达式

Question

我正试图使用递归查询来查找通过如下结构的表的路径：

关联实体

FromKey TINYINT
ToKey TINYINT
...more....

我想我可以做这样的事情：

DECLARE @startKey UNIQUEIDENTIFIER, @endKey UNIQUEIDENTIFIER;
SET @startKey = 0;
SET @endKey = 3;
;With findPath
AS
(
    SELECT FromKey, ToKey
    FROM RelatedEntities
    WHERE FromKey = @startKey
    UNION ALL
    SELECT FromKey, ToKey
    FROM RelatedEntities r
    JOIN findPath b
    ON r.FromKey = b.ToKey
    AND r.FromKey NOT IN (SELECT FromKey FROM b)
)
SELECT * FROM findPath;

此代码失败，因为我无法在CTE中使用子查询。递归查询只能包含一个对CTE的引用，这似乎也是一条规则。（真的吗？）也许这是一个游标或过程代码的作业，但我想我会把它放在这里，以防我错过了一种在带有CTE的表中找到路径的方法？

参数为：

1. 从开始和结束键开始
2. 基本查询使用起始键
3. 当递归查询包含结束键时，它应该停止（无法计算出），并且不应该重复开始键
4. MAXRECURSION选项可用于在一定次数的迭代后停止

感谢你们所有的CTE大师。让我说清楚。

为了可读性，将其从UNIQUEIDENTIFIERS更改为TINYINT。SQL构造是相同的。这里有一些测试代码。

CREATE TABLE RelatedEntities(FromKey TINYINT, ToKey TINYINT);
INSERT RelatedEntities(FromKey, ToKey)
VALUES
(1, 0),
(0, 1), 
(1, 7),
(7, 1),
(3, 4),
(4, 3)
;With FindPath 
AS
(
    SELECT FromKey, ToKey, 0 AS recursionLevel
    FROM RelatedEntities
    WHERE FromKey = 1
    UNION ALL
    SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel + 1
    FROM RelatedEntities r
    INNER JOIN FindPath b ON r.FromKey = b.ToKey
    WHERE b.ToKey <> 3 AND RecursionLevel < 10
)
SELECT * FROM FindPath
ORDER BY recursionLevel

请注意，这将从1返回到0，然后从0返回到1，并重复执行，直到用完递归级别。

Answer 1

您需要这样修改查询：

DECLARE @startKey UNIQUEIDENTIFIER, @endKey UNIQUEIDENTIFIER;
DECLARE @maxRecursion INT = 100
SET @startKey = '00000000-0000-0000-0000-000000000000';
SET @endKey = 'F7801327-C037-AA93-67D1-B7892F6093A7';
;With FindPath
AS
(
    SELECT FromKey, ToKey, 0 AS recursionLevel
    FROM RelatedEntities
    WHERE FromKey = @startKey
    UNION ALL
    SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel +1
    FROM RelatedEntities r
    INNER JOIN FindPath b ON r.FromKey = b.ToKey
    WHERE b.ToKey <> @endKey AND recursionLevel < @maxRecursion
)
SELECT * FROM FindPath;

上述CTE:的锚定成员

SELECT FromKey, ToKey, 0 AS recursionLevel
FROM RelatedEntities
WHERE FromKey = @startKey

将选择（From，To）记录链的起始记录T0。

CTE:的递归成员

SELECT r.FromKey, r.ToKey, recursionLevel = recursionLevel +1
FROM RelatedEntities r
INNER JOIN FindPath b ON r.FromKey = b.ToKey
WHERE b.ToKey <> @endKey AND recursionLevel < @maxRecursion

将与T0、T1、…一起执行。。。作为输入和T1、T2。。。分别作为输出。

此过程将继续将记录添加到最终结果集，直到递归成员返回空集，即，直到具有ToKey=@endKey的记录添加到结果集，达到或@maxRecursion级别。

编辑：

您可以使用以下查询来有效处理任何循环路径：

;With FindPath 
AS
(
    SELECT FromKey, ToKey, 
           0 AS recursionLevel,
           CAST(FromKey AS VARCHAR(MAX)) AS FromKeys
    FROM RelatedEntities
    WHERE FromKey = 1
    UNION ALL
    SELECT r.FromKey, r.ToKey, 
           recursionLevel = recursionLevel + 1,
           FromKeys = FromKeys + ',' + CAST(r.FromKey AS VARCHAR(MAX))
    FROM RelatedEntities r
    INNER JOIN FindPath b ON r.FromKey = b.ToKey
    WHERE (b.ToKey <> 3) 
          AND (RecursionLevel < 10) 
          AND PATINDEX('%,' + CAST(r.ToKey AS VARCHAR(MAX)) + ',%', ',' + FromKeys + ',') = 0  
)
SELECT * FROM FindPath
ORDER BY recursionLevel

计算字段FromKeys用于将FromKey继续到下一个递归级别。通过这种方式，以前递归级别的任何键都是使用字符串串联逐层累积的。然后使用CCD_ 12来检查是否已经满足圆形路径。

SQL Fiddle演示