SQL Server: Как ограничить рекурсию CTE строками просто recursivly добавленный?

В стороне: у Вас есть SQL Server 2008? Это могло бы подходить hierarchyid тип данных.

Если я понимаю Ваши намерения, можно получить Вас результат путем выполнения чего-то вроде этого:

DECLARE @StartID INT;
SET @StartID = 1;
WITH CTE (ChildNodeID, ParentNodeID, [Level]) AS
(
  SELECT  t1.ChildNodeID, 
          t1.ParentNodeID, 
          0
  FROM tblNodes AS t1
  WHERE ChildNodeID = @StartID
  UNION ALL
  SELECT  t1.ChildNodeID, 
          t1.ParentNodeID, 
          t2.[Level]+1
  FROM tblNodes AS t1
    INNER JOIN CTE AS t2 ON t1.ParentNodeID = t2.ChildNodeID    
)
SELECT t1.ChildNodeID, t2.ChildNodeID, t1.[Level]- t2.[Level] AS GenerationsDiff
FROM CTE AS t1
  CROSS APPLY CTE t2

Это возвратит различие в поколении между всеми узлами, можно изменить его для Вас, требуют потребности.

Ну, Ваш ответ не совсем настолько очевиден :-)

WITH (NodeChildren) AS
{
   --initialization
   SELECT ParentNodeID, ChildNodeID, 1 AS GenerationsRemoved
   FROM Nodes

Эту часть называют частью "привязки" рекурсивного CTE - но это должно действительно только выбрать один или выбор немного строк от Вашей таблицы - это выбирает все!

Я предполагаю то, что Вы пропускаете, вот просто подходящий оператор Where:

WITH (NodeChildren) AS
{
   --initialization
   SELECT ParentNodeID, ChildNodeID, 1 AS GenerationsRemoved
   FROM Nodes
   **WHERE ParentNodeID IS NULL**

Однако я боюсь, что Ваше требование, чтобы не иметь просто "прямой" иерархии, но также и дочерних прародителем строк, не могло бы быть настолько легко удовлетворить.... обычно, рекурсивный CTE будет только когда-либо показывать один уровень и его прямых подчиненных (и что вниз иерархия, конечно) - это обычно не пропускает один, два или еще больше уровней.

Надежда это помогает немного.

Marc

Проблема с пределом рекурсии значения по умолчанию SQL-сервера (100). Если Вы пробуете свой пример наверху удаленным ограничением привязки (также добавленный Порядок):

WITH NodeChildren AS
(
   --initialization
   SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
   FROM Nodes

   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
ORDER BY ParentNodeID ASC

Это приводит к желаемым результатам. Проблема Вы, которые направление Ааре с большим числом строк, Вы будете recirse более чем 100 раз, который является пределом по умолчанию. Это может быть изменено путем добавления option (max recursion x) после Вашего запроса, где x является числом между 1 и 32767. x может также быть установлен на 0, который не устанавливает предела, однако мог очень быстро оказать очень вредное влияние на Вашу производительность сервера. Очевидно как количество строк в увеличениях Узлов, число желания рекурсий может увеличиться очень быстро, и я избежал бы этого подхода, если не был известный верхний предел строк в таблице. Для полноты заключительный запрос должен быть похожим:

 WITH NodeChildren AS
    (
       --initialization
       SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
       FROM Nodes

       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 * 
    FROM NodeChildren
    ORDER BY ParentNodeID
    OPTION (MAXRECURSION 32767)

Где 32767 мог быть уменьшен для установки сценарию

Вы попытались создать путь в CTE и использовать его для идентификации предков?

Можно затем вычесть порожденную глубину узла из глубины узла предка для вычисления столбца GenerationsRemoved, как так...

DECLARE @Nodes TABLE
(
    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')

DECLARE @Hierarchy TABLE
(
    NodeId varchar(50) PRIMARY KEY NOT NULL,
    ParentNodeId varchar(50) NULL,
    Depth int NOT NULL,
    [Path] varchar(2000) NOT NULL
)

WITH Hierarchy AS
(
    --initialization
    SELECT NodeId, ParentNodeId, 0 AS Depth, CONVERT(varchar(2000), NodeId) AS [Path]
    FROM @Nodes
    WHERE ParentNodeId IS NULL

    UNION ALL

    --recursive execution
    SELECT n.NodeId, n.ParentNodeId, p.Depth + 1, CONVERT(varchar(2000), p.[Path] + '/' + n.NodeId)
    FROM Hierarchy AS p
    INNER JOIN @Nodes AS n
    ON p.NodeId = n.ParentNodeId
)
INSERT INTO @Hierarchy
SELECT *
FROM Hierarchy

SELECT parent.NodeId AS AncestorNodeId, child.NodeId AS DescendantNodeId, child.Depth - parent.Depth AS GenerationsRemoved
FROM @Hierarchy AS parent
INNER JOIN @Hierarchy AS child
ON child.[Path] LIKE parent.[Path] + '/%'