CTE进行递归查询

Question

我正在尝试使用CTE查询层次结构数据，但我陷入了以下情况。我的数据库中有以下数据：

dbo.projectComponents

ID207

dbo.projectAccessories

ID ProjectComponentID cocessorySetID4 34 null5 145空6 207空8 NULL 3

dbo.projectAccessorySetsets

id cockestoryId1个空2空3 6

我想查询所有dbo.projectAccessory.id projectComponent。

以下查询引发此错误：Error 530: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

WITH AccessoryIds AS
(SELECT parent.Id, accessorySets.AccessoryId
FROM dbo.ProjectAccessories AS parent, dbo.ProjectAccessorySets AS accessorySets
WHERE parent.ProjectComponentId = 207 AND accessorySets.AccessoryId = parent.Id
UNION ALL
SELECT child.Id, childAccessorySets.AccessoryId
FROM dbo.ProjectAccessories AS child, dbo.ProjectAccessorySets AS childAccessorySets
INNER JOIN AccessoryIds AS asi ON childAccessorySets.AccessoryId = asi.Id
WHERE childAccessorySets.AccessoryId = child.Id AND childAccessorySets.AccessoryId IS NOT NULL)
SELECT * FROM AccessoryIds

预期输出

id cockestoryId6空8 6

编辑

添加option (maxrecursion 0)无法解决问题，并使查询永远运行。接下来，目前我的数据库中的递归深度仅为2(请参阅添加数据(

Answer 1

看起来这样做：

DECLARE @ProjectAccessories TABLE
(
    Id INT, ProjectComponentId INT, AccessorySetId INT 
)
DECLARE @ProjectAccessorySets TABLE
(
    Id INT, AccessoryId INT
)
INSERT @ProjectAccessories (Id, ProjectComponentId, AccessorySetId)
VALUES (4, 34, NULL)
,       (5, 145, NULL)
,       (6, 207, NULL)
,       (8, NULL, 3)
INSERT @ProjectAccessorySets (Id, AccessoryId)
VALUES (1, NULL)
,       (2, NULL)
,       (3, 6) 
;
WITH AccessoryIds AS
(
        SELECT      parent.Id
        ,           parent.AccessorySetId AS AccessoryId
        FROM        @ProjectAccessories AS parent
        INNER JOIN  @ProjectAccessorySets AS accessorySets
                ON  accessorySets.AccessoryId = parent.Id
        WHERE       parent.ProjectComponentId = 207
        UNION ALL
        SELECT      child.id
        ,           childAccessorySets.AccessoryId
        FROM        AccessoryIds AS asi 
        INNER JOIN  @ProjectAccessorySets AS childAccessorySets
                ON  childAccessorySets.AccessoryId = asi.Id
        INNER JOIN  @ProjectAccessories AS child
                ON  child.AccessorySetId = childAccessorySets.id
)
SELECT  * 
FROM    AccessoryIds

导致：

+----+-------------+
| Id | AccessoryId |
+----+-------------+
|  6 | NULL        |
|  8 | 6           |
+----+-------------+