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

Ответ 1

Попробуйте следующее:

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

Ответ 2

Кроме того: у вас есть SQL Server 2008? Это может подойти для hierarchyid типа данных.

Ответ 3

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

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

Это вернет разницу поколений между всеми узлами, вы можете изменить ее для получения точных потребностей.

Ответ 4

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

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

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

Марк

Ответ 5

Проблема связана с лимитом рекурсии 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 можно отрегулировать вниз, чтобы соответствовать вашему сценарию

Ответ 6

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

Затем вы можете вычесть глубину node потомка из глубины предка node, чтобы вычислить столбец 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] + '/%'

Ответ 7

Это нарушает предел рекурсии, наложенный на ответ Криса Шаффера.

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

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 IS NOT NULL), созданное удаление начинается с 2. Мы можем затем идентифицировать циклы, проверив (P.ParentNodeID == N.NodeID), который мы просто надеваем ' t добавить его. Впоследствии мы добавим пропущенное поколение 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

Ответ 8

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

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