给定一个以子行开始的递归查询，如何消除同级行和父行

Question

我成功地构建了一个递归查询，该查询返回所选Id及其所有子项的行。这对最终的父母来说绝对有效，但当传递的Id是其中一个孩子的Id时，我也需要它正确工作，只显示孩子及其孩子(如果有的话(。目前，它仍然返回最终父行的其他子行加上传递的子行显示两次。。。

与之前的问题一样，我必须使用子查询格式来完成这项工作，因为可能会使用除SQL Server之外的其他基于TSQL的数据库引擎，这些引擎不支持CTE或with子句。

预期结果：

使用Id 2，将返回正确的数据：2、3、4、6、7。使用Id 6，它应该只返回6,7。当前查询返回6,3,4,6,7。

数据：

ProjectId   ProjectName                             ParentId
1           Test Project                            -1
2           Test Project 2                          0
3           Test Project 2 Sub Project 1            2
4           Test Project 2 Sub Project 2            2
5           Test Project 3                          -1
6           Test Project 2 Sub Sub Project 1        3
7           Test Project 2 Sub Sub Sub Project 1    6

查询：

DECLARE @PROJECTID BIGINT = 2;
SELECT *
FROM            
(
SELECT *
FROM ProjectCostingProjects pcp
WHERE pcp.[ProjectId] = @PROJECTID 
UNION ALL
SELECT  pcp2.*
FROM    ProjectCostingProjects pcp2
JOIN    ProjectCostingProjects pcp
ON     pcp2.ParentID = pcp.ProjectId
);

收到任何建议或建议，不胜感激。

Answer 1

所以。。。递归公共表表达式不仅仅是使用union all的函数，它还在union all的第二部分中使用公共表表达式的自引用。不能简单地在另一个RDBMS上复制这种递归操作，方法是尝试将其设置为子查询/派生表。

如果您希望在SQL Server中进行递归层次结构遍历，最好的选择是使用递归公共表表达式。

declare @projectid bigint = 6;
;with cte as (
select *
from ProjectCostingProjects pcp
where pcp.[ProjectId] = @projectid 
union all
select  pcp2.*
from    ProjectCostingProjects pcp2
inner join cte 
on pcp2.Parentid = cte.ProjectId
)
select *
from cte;

rextester演示：http://rextester.com/XON59636

退货：

+-----------+--------------------------------------+----------+
| ProjectId |             ProjectName              | ParentId |
+-----------+--------------------------------------+----------+
|         6 | Test Project 2 Sub Sub Project 1     |        3 |
|         7 | Test Project 2 Sub Sub Sub Project 1 |        6 |
+-----------+--------------------------------------+----------+

---

您现在的查询将(在添加别名后(返回@ProjectID行和3、4、6、7行。因为您所写的内容将返回等于@ProjectID的任何一行，以及所有具有父行(不是0或-1(的行，这些行是ProjectId为3、4、6、7的行。

带有@ProjectId = null的rextester演示：http://rextester.com/VQU71307

Answer 2

这里有一个使用while循环的答案。

解决方案1，使用while循环和临时表

/* Populating the temp table with the data */
DECLARE @recurse TABLE
(projectId INT,parent int);
INSERT INTO @recurse (projectId,parent) VALUES (1,-1);
INSERT INTO @recurse (projectId,parent) VALUES (2,-0);
INSERT INTO @recurse (projectId,parent) VALUES (3,2);
INSERT INTO @recurse (projectId,parent) VALUES (4,2);
INSERT INTO @recurse (projectId,parent) VALUES (5,-1);
INSERT INTO @recurse (projectId,parent) VALUES (6,3);
INSERT INTO @recurse (projectId,parent) VALUES (7,6);
DECLARE @recurse2 TABLE
(projectId INT,parent INT);
--Start by inserting the root element
INSERT INTO @recurse2 ( projectId, parent)
SELECT * FROM @recurse WHERE projectId = 2
--insert elements until all children have all children
WHILE EXISTS (SELECT * FROM @recurse WHERE parent IN (SELECT projectId FROM @recurse2) AND projectId NOT IN (SELECT projectId FROM @recurse2) )
BEGIN
INSERT INTO @recurse2
(
projectId,
parent
)
SELECT * FROM @recurse WHERE parent IN (SELECT projectId FROM @recurse2) AND projectId NOT IN (SELECT projectId FROM @recurse2)
END 
SELECT * FROM @recurse2

解决方案2为了提高性能，您可以使用while循环的结果创建一个间歇表。这个中间表可以在intervall上更新，也可以作为在主表中创建元素的一部分进行更新。这可以是业务逻辑的一部分，也可以是DB触发器。

如果你想把它作为一项预定的工作，我会写以下代码：

-- Populating the temp table with the data 
DECLARE @recurse TABLE
(projectId INT,parent int);
INSERT INTO @recurse (projectId,parent) VALUES (1,-1);
INSERT INTO @recurse (projectId,parent) VALUES (2,-0);
INSERT INTO @recurse (projectId,parent) VALUES (3,2);
INSERT INTO @recurse (projectId,parent) VALUES (4,2);
INSERT INTO @recurse (projectId,parent) VALUES (5,-1);
INSERT INTO @recurse (projectId,parent) VALUES (6,3);
INSERT INTO @recurse (projectId,parent) VALUES (7,6);
DECLARE @recurse2 TABLE
(projectId INT,parent INT, lvl int);
--Start by inserting all elements root at lvl 0
INSERT INTO @recurse2 ( projectId, parent, lvl ) SELECT projectId, parent, 0 FROM @recurse 
SELECT * FROM @recurse2
--insert elements until we have all parents for all elements
WHILE EXISTS (SELECT * FROM @recurse2 a WHERE lvl IN (SELECT TOP 1 lvl FROM @recurse2 b WHERE a.projectId = b.projectId ORDER BY lvl DESC) AND a.parent > 0 )
BEGIN
--Insert the next parent for all elements that does not have a their top level parent allready
INSERT INTO @recurse2 ( projectId, parent , lvl )
SELECT  projectId, 
(SELECT b.parent FROM @recurse b WHERE b.projectId = a.parent), 
lvl + 1 
FROM @recurse2 a WHERE lvl IN (SELECT TOP 1 lvl FROM @recurse2 b WHERE a.projectId = b.projectId ORDER BY lvl DESC) AND a.parent > 0 
END 
--Find all children to an element
SELECT * FROM @recurse2 WHERE parent = 2

解决方案#2的最大优点是性能应该非常适合读取，甚至可能比CTE更好。此外，它也适用于从下到上和从上到下的阅读。