在CTE SQL查询中选择多个递归联合。

Question

我正在尝试构建一个查询，用于使用CTE查询从表中搜索所有父行和子行。

我既可以搜索父母，也可以返回子女，也可以搜索子女和返回父母，但我不能在一个查询中同时处理这两种可能性。

我只能完成一个联盟所有的疑问。

我哪里出问题了？

declare @search nvarchar(50)

--set @search = '%Nucleus sub project%';
set @search = '%Nucleus test';

WITH Parent AS
(
    SELECT * 
    FROM tblProjects 
    WHERE ProjNo LIKE @search 
       OR ProjDes LIKE @search

    UNION ALL

    SELECT tblProjects.* 
    FROM tblProjects  
    JOIN Parent ON tblProjects.proID = Parent.ParentProjID

    UNION ALL

    SELECT tblProjects.* 
    FROM tblProjects  
    JOIN Parent ON tblProjects.ParentProjID = Parent.proID 
)
SELECT distinct * 
FROM Parent 
ORDER BY ParentProjID

我收到一个错误：

陈述结束了。最大递归100已在语句完成之前耗尽。

Answer 1

尝试使用两个不同的CTE：

WITH x AS (
    SELECT p.*
    FROM tblProjects p
    WHERE  ProjNo LIKE @search OR ProjDes LIKE @search
   ),
   parents as (
    SELECT * 
    FROM x 
    UNION ALL
    SELECT p.* 
    FROM parents JOIN
         tblProjects p
         ON p.parentid= parents.proID
   ),
   children as (
    SELECT * 
    FROM x 
    UNION ALL
    SELECT p.* 
    FROM children JOIN
         tblProjects p
         ON children.parentid = p.proID
   )
SELECT distinct * 
FROM parents
UNION
SELECT distinct *
FROM children;

Answer 2

在UNION ALL之后的第二个select被递归地调用，直到没有更多的行被添加到结果集中。

是这样的；

1. 您不需要第三个SELECT语句。第二个应该抓住一切。
2. 如果您有循环(例如，A的父级是B，B的父级是A)，则会得到无限递归，而SQL将在100次迭代后退出，从而给出最大递归错误。

检测周期比较复杂，但可以完成。