SQL Server:如何将CTE递归限制为只是递归添加的行？

试试这个:

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个以满足您的需要).然后添加排序,使其看起来像您想要的结果.

试试这个:

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个以满足您的需要).然后添加排序,使其看起来像您想要的结果.