SQL Server：如何将CTE递归限制为仅递归添加的行？ [英] SQL Server: How to limit CTE recursion to rows just recursivly added?
问题描述
让我们尝试一个更简单的示例，这样人们可以全神贯注于概念，并有一个实际的示例，您可以将其复制并粘贴到SQL查询中分析器：
Let's try a simpler example, so people can wrap their heads around the concepts, and have a practical example that you can copy&paste into SQL Query Analizer:
想象一个具有层次结构的 Nodes 表：
Imagine a Nodes table, with a heirarchy:
A
- B
- C
我们可以在Query Analizer中开始测试：
We can start testing in Query Analizer:
CREATE TABLE ##Nodes
(
NodeID varchar(50) PRIMARY KEY NOT NULL,
ParentNodeID varchar(50) NULL
)
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('A', null)
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('B', 'A')
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('C', 'B')
所需输出：
ParentNodeID NodeID GenerationsRemoved
============ ====== ==================
NULL A 1
NULL B 2
NULL C 3
A B 1
A C 2
B C 1
现在建议使用CTE表达式，但输出不正确：
Now the suggested CTE expression, with it's incorrect output:
WITH NodeChildren AS
(
--initialization
SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
FROM ##Nodes
WHERE ParentNodeID IS NULL
UNION ALL
--recursive execution
SELECT P.ParentNodeID, N.NodeID, P.GenerationsRemoved + 1
FROM NodeChildren AS P
INNER JOIN ##Nodes AS N
ON P.NodeID = N.ParentNodeID
)
SELECT ParentNodeID, NodeID, GenerationsRemoved
FROM NodeChildren
实际输出：
ParentNodeID NodeID GenerationsRemoved
============ ====== ==================
NULL A 1
NULL B 2
NULL C 3
注意：如果使用SQL Server 2005 †CTE无法做我2000年以前的工作‡，没关系，这就是答案。只要答案是不可能，谁就会赢得赏金。但是我要等几天，以确保每个人都同意，在我无法解决我的问题的250个声誉之前，这是不可能的。
Note: If SQL Server 2005† CTE cannot do what i was doing before in 2000‡, that's fine, and that's the answer. And whoever gives "it's not possible" as the answer will win the bounty. But i will wait a few days to make sure everyone concur's that it's not possible before i irrecovably give 250 reputation for a non-solution to my problem.
Nitpickers角落
†不是2008
‡无需求助于UDF *，这是解决方案
‡without resorting to a UDF*, which is the solution already have
*除非您在原始问题中看不到提高UDF性能的方法
*unless you can see a way to improve the performance of the UDF in the original question
我有一个节点表，每个节点都有一个父节点指向另一个节点（或指向空）。
i have a table of Nodes, each with a parent that points to another Node (or to null).
举例说明：
1 My Computer
2 Drive C
4 Users
5 Program Files
7 Windows
8 System32
3 Drive D
6 mp3
我想要一个表，该表返回所有父子关系以及它们之间的世代数
i want a table that returns all the parent-child relationships, and the number of generations between them
对于所有可怕ct父关系：
For for all direct parent relationships:
ParentNodeID ChildNodeID GenerationsRemoved
============ =========== ===================
(null) 1 1
1 2 1
2 4 1
2 5 1
2 7 1
1 3 1
3 6 1
7 8 1
但是这里有祖父母关系：
But then there's the grandparent relationships:
ParentNodeID ChildNodeID GenerationsRemoved
============ =========== ===================
(null) 2 2
(null) 3 2
1 4 2
1 5 2
1 7 2
1 6 2
2 8 2
并且有曾祖父母之间的关系：
And the there's the great-grand-grandparent relationships:
ParentNodeID ChildNodeID GenerationsRemoved
============ =========== ===================
(null) 4 3
(null) 5 3
(null) 7 3
(null) 6 3
1 8 3
所以我可以弄清楚基本的CTE初始化：
So i can figure out the basic CTE initialization:
WITH (NodeChildren) AS
{
--initialization
SELECT ParentNodeID, NodeID AS ChildNodeID, 1 AS GenerationsRemoved
FROM Nodes
}
现在的问题是递归部分。当然，显而易见的答案是行不通的：
The problem now is the recursive part. The obvious answer, of course, doesn't work:
WITH (NodeChildren) AS
{
--initialization
SELECT ParentNodeID, ChildNodeID, 1 AS GenerationsRemoved
FROM Nodes
UNION ALL
--recursive execution
SELECT parents.ParentNodeID, children.NodeID, parents.Generations+1
FROM NodeChildren parents
INNER JOIN NodeParents children
ON parents.NodeID = children.ParentNodeID
}
Msg 253, Level 16, State 1, Line 1
Recursive member of a common table expression 'NodeChildren' has multiple recursive references.
初始CTE表中包含生成整个递归列表所需的所有信息。但是，如果不允许这样做，我会尝试：
All the information needed to generate the entire recursive list is present in the inital CTE table. But if that's not allowed i'll try:
WITH (NodeChildren) AS
{
--initialization
SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
FROM Nodes
UNION ALL
--recursive execution
SELECT parents.ParentNodeID, Nodes.NodeID, parents.Generations+1
FROM NodeChildren parents
INNER JOIN Nodes
ON parents.NodeID = nodes.ParentNodeID
}
但这失败了，因为它不仅联接了递归元素，而且不断地递归地添加相同的行：
But that fails because it's not only joining on the recursive elements, but keeps recursivly adding the same rows over and over:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
在SQL Server 2000中，我使用用户定义函数（UDF）模拟了CTE：
In SQL Server 2000 i simulated a CTE by using a User Defined Function (UDF):
CREATE FUNCTION [dbo].[fn_NodeChildren] ()
RETURNS @Result TABLE (
ParentNodeID int NULL,
ChildNodeID int NULL,
Generations int NOT NULL)
AS
/*This UDF returns all "ParentNode" - "Child Node" combinations
...even multiple levels separated
BEGIN
DECLARE @Generations int
SET @Generations = 1
--Insert into the Return table all "Self" entries
INSERT INTO @Result
SELECT ParentNodeID, NodeID, @Generations
FROM Nodes
WHILE @@rowcount > 0
BEGIN
SET @Generations = @Generations + 1
--Add to the Children table:
-- children of all nodes just added
-- (i.e. Where @Result.Generation = CurrentGeneration-1)
INSERT @Result
SELECT CurrentParents.ParentNodeID, Nodes.NodeID, @Generations
FROM Nodes
INNER JOIN @Result CurrentParents
ON Nodes.ParentNodeID = CurrentParents.ChildNodeID
WHERE CurrentParents.Generations = @Generations - 1
END
RETURN
END
阻止它爆炸的魔力是where子句的限制：
WHERE CurrentParents.Generations-@ Generations-1
And the magic to keep it from blowing up was the limiting where clause: WHERE CurrentParents.Generations - @Generations-1
如何防止递归CTE永远递归？
How do you prevent a recursive CTE from recursing forever?
推荐答案
尝试一下：
WITH Nodes AS
(
--initialization
SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
FROM ##Nodes
UNION ALL
----recursive execution
SELECT P.ParentNodeID, N.NodeID, P.GenerationsRemoved + 1
FROM Nodes AS P
INNER JOIN ##Nodes AS N
ON P.NodeID = N.ParentNodeID
WHERE P.GenerationsRemoved <= 10
)
SELECT ParentNodeID, NodeID, GenerationsRemoved
FROM Nodes
ORDER BY ParentNodeID, NodeID, GenerationsRemoved
从初始化中基本删除了仅显示绝对父母查询这样，它从每个结果开始生成结果，然后从那里开始递减结果。我还添加了 WHERE P.GenerationsRemoved< = 10作为无限递归捕获（将10替换为最多100的任何数字以满足您的需求）。然后添加排序，使其看起来像您想要的结果。
Basically removing the "only show me absolute parents" from the initialization query; That way it generates the results starting from each of them and decending from there. I also added in the "WHERE P.GenerationsRemoved <= 10" as an infinite recursion catch(replace 10 with any number up to 100 to fit your needs). Then add the sort so it looks like the results you wanted.
这篇关于SQL Server：如何将CTE递归限制为仅递归添加的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
