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

В основном удаление «показать мне только абсолютных родителей» из запроса инициализации; Таким образом, он генерирует результаты, начиная с каждого из них и заканчивая оттуда. Я также добавил в «ГДЕ P.GenerationsRemoved ‹= 10» как улов бесконечной рекурсии (замените 10 на любое число до 100, чтобы оно соответствовало вашим потребностям). Затем добавьте сортировку, чтобы результаты выглядели так, как вы хотели.

В сторону: у вас есть 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 всегда будет показывать только один уровень и его прямые подчиненные ( и это вниз по иерархии, конечно) - обычно он не пропускает один, два или даже больше уровней.

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

Марк

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

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

Это дает желаемые результаты. Проблема, с которой вы сталкиваетесь, связана с большим количеством строк, которые вы будете повторять более 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] + '/%'

Это нарушает ограничение рекурсии, наложенное на ответ Криса Шаффера.

Я создаю таблицу с циклом:

CREATE TABLE ##Nodes
(
   NodeID varchar(50) PRIMARY KEY NOT NULL,
   ParentNodeID varchar(50) NULL
)

INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('A', 'C');
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('B', 'A');
INSERT INTO ##Nodes (NodeID, ParentNodeID) VALUES ('C', 'B');

В случаях, когда существует потенциальный цикл (т.е. ParentNodeId НЕ NULL), удаление генерации начинается с 2. Затем мы можем идентифицировать циклы, проверяя (P.ParentNodeID == N.NodeID), который мы просто не добавляем. . После этого мы добавляем пропущенное поколение remove = 1.

WITH ParentNodes AS
(
   --initialization
   SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved
   FROM ##Nodes
   WHERE ParentNodeID IS NULL

   UNION ALL

   SELECT P.ParentNodeID, N.NodeID, 2 AS GenerationsRemoved
   FROM ##Nodes N
   JOIN ##Nodes P ON N.ParentNodeID=P.NodeID
   WHERE P.ParentNodeID IS NOT NULL

   UNION ALL

   ----recursive execution
   SELECT P.ParentNodeID, N.NodeID, P.GenerationsRemoved + 1
   FROM ParentNodes AS P
     INNER JOIN ##Nodes AS N
     ON P.NodeID = N.ParentNodeID
   WHERE P.ParentNodeID IS NULL OR P.ParentNodeID <> N.NodeID

),
Nodes AS (
   SELECT ParentNodeID, NodeID, 1 AS GenerationsRemoved 
   FROM ##Nodes 
   WHERE ParentNodeID IS NOT NULL

   UNION ALL

   SELECT ParentNodeID, NodeID, GenerationsRemoved FROM ParentNodes
)
SELECT ParentNodeID, NodeID, GenerationsRemoved
FROM Nodes
ORDER BY ParentNodeID, NodeID, GenerationsRemoved

with cte as
(
    select a=65, L=1
    union all
    select a+1, L=L+1
    from cte
    where L<=100
)
select 
IsRecursion=Case When L>1 then 'Recursion' else 'Not Recursion' end,
AsciiValue=a,
AsciiCharacter=char(a)
from cte

1. Создайте столбец, содержащий текущий уровень.
2. Проверьте, если уровень> 1

В моем примере показан рекурсивный CTE, который останавливает рекурсию после 100 уровней (максимум). В качестве бонуса он отображает набор символов ASCII и соответствующее числовое значение.