Рекурсивный запрос SQL Server в проблеме CTE
У меня есть таблица Nodes, состоящая из базового документа, текущего документа и целевого документа:
BaseDocType . BaseDocID
DocType . DocID
TargetDocType . TargetDocID ..
Я хочу получить все связанные узлы для любого конкретного узла. Это то, что у меня есть до сих пор:
WITH CTE1 (ID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID) AS
(
SELECT
ID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
FROM
Doc.Nodes
WHERE
DocType = 8 AND DocID = 2
UNION ALL
SELECT
a.ID, a.BaseDocType, a.BaseDocID, a.DocType, a.DocID, a.TargetDocType, a.TargetDocID
FROM
Doc.Nodes a
INNER JOIN
CTE1 b ON (a.BaseDocType = a.BaseDocType
AND a.BaseDocID = b.BaseDocID
AND a.DocType != b.DocType
AND a.DocID != b.DocID)
)
SELECT *
FROM CTE1
Но запрос не работает. Я получаю эту ошибку:
Сообщение 530, уровень 16, состояние 1, строка 8
Заявление прекращено. Максимальная рекурсия 100 была исчерпана до завершения оператора.
![Пример][1]
Как я могу это исправить?
[вот пример, которого я пытаюсь достичь][2]
Я надеюсь, что вы присядете за это, но люди здесь не знакомы с вашей базой данных и не могут сделать вывод о том, как работают отношения между различными столбцами DocID, которые вы упомянули, поэтому вы, возможно, захотите это объяснить.
Проверьте, что говорит ошибка. Вы действительно ожидаете, что в вашей иерархии будет более 100 уровней? Если нет, то либо запрос неправильный, либо данные неправильные, и строки оказываются в кружках. Ограничение уровня в 100 предназначено для выявления таких ошибок. Если вы ожидаете более 100 уровней, серьезно рассмотрите возможность использования вместо этого столбца иерархии.
Что ты вообще пытаешься сделать? Запрос определенно создает циклы. На изображении, которое вы разместили, обе строки удовлетворяют условию рекурсии по отношению друг к другу, поэтому только этих двух строк достаточно для создания бесконечной рекурсии. Нет четкой ID-ParentID связи. Это похоже на двусторонний граф, который невозможно представить в виде иерархии или с помощью рекурсивного CTE (по крайней мере, без очень сложного запроса). Для этого и нужны графические таблицы.
Рассмотрим на первом уровне конкретную пару BaseDocType и BaseDocID. Скажите (1,1). Вы ссылаетесь на строку с разными DocType и DocID на втором уровне. Но затем на третьем уровне вы можете создать обратную ссылку на строку на первом уровне, поскольку ее DocType и DocID будут отличаться от второго уровня. Таким образом вы получаете петли. Короче говоря, ваш запрос не имеет смысла.
спасибо за ваше время, ребята. Я загрузил еще одно изображение, которое иллюстрирует, что именно я пытаюсь сделать... я пытаюсь сделать что-то похожее на документ о взаимоотношениях сущностей... поэтому я решил создать таблицу отслеживания, в которой будет храниться текущий документ с родительским и дочерним элементами... другими словами... следующий документ ... предыдущий документ для отображения связи в форме
Пожалуйста, не используйте изображения, используйте уценку таблицы.
Панайотис Канавос... именно это я и пытаюсь сделать. У меня есть заказы на продажу, связанные с другими маркетинговыми документами. и другие документы, которые были связаны с этими заказами на поставку, могут быть связаны с другими объектами. Я хочу перечислить семейство иерархии для любого конкретного документа..
Ответы 1
Проблема в том, что строка привязки (ID = 2) находит связанную строку с ID = 1, но связь работает и в другом направлении. Таким образом, строка 1 затем находит строку 2, и так до бесконечности. Вам нужно какое-то условие завершения рекурсии.
Я не уверен, почему данная модель имеет отношения база/документ/цель и не имеет целевых данных.
Некоторые настроены с использованием табличных переменных:
DECLARE @Nodes TABLE (ID INT, BaseDocType INT, BaseDocID INT, DocType INT, DocID INT, TargetDocType INT NULL, TargetDocID INT NULL);
INSERT INTO @Nodes
VALUES
(1, 10, 6, 100, 2034, NULL, NULL),
(2, 10, 6, 8, 2, NULL, NULL);
Вместо того, чтобы указывать детали начального документа в привязке, вы можете передать их через:
With CTE1 (ID, StartDocType, StartDocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID)
As
(
Select ID, DocType, DocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
From @Nodes
Union All
Select a.ID, b.StartDocType, b.StartDocID, a.BaseDocType, a.BaseDocID, a.DocType, a.DocID, a.TargetDocType, a.TargetDocID
From @Nodes a
INNER JOIN CTE1 b
ON (
a.BaseDocType = b.BaseDocType
AND a.BaseDocID = b.BaseDocID
AND a.DocType != b.DocType
AND a.DocID != b.DocID
AND NOT (a.DocType = b.StartDocType AND a.DocID = b.StartDocID)
)
)
Select *
From CTE1
Where StartDocType=8 and StartDocID = 2
Это помогает, поскольку рекурсивная часть может избежать повторной обработки начальной строки и возврата к началу. Однако, имея больше данных, вы вполне можете снова столкнуться с той же проблемой.
Самый простой способ прекратить рекурсию — это самостоятельно ограничить глубину рекурсии, отслеживая глубину и ограничивая ее в рекурсивной части:
With CTE1 (rlevel, ID, StartDocType, StartDocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID)
As
(
Select 1, ID, DocType, DocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
From @Nodes
Union All
Select b.rlevel + 1, a.ID, b.StartDocType, b.StartDocID, a.BaseDocType, a.BaseDocID, a.DocType, a.DocID, a.TargetDocType, a.TargetDocID
From @Nodes a
INNER JOIN CTE1 b
ON (
a.BaseDocType = b.BaseDocType
AND a.BaseDocID = b.BaseDocID
AND a.DocType != b.DocType
AND a.DocID != b.DocID
)
WHERE b.rlevel < 10
)
Select DISTINCT ID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
From CTE1
Where StartDocType=8 and StartDocID = 2
Возможно, существуют более эффективные способы ограничения рекурсии, но для этого потребуются дополнительные знания базовых данных.
миллион спасибо тебе, Стивен.. Я постараюсь проверить твою идею.. только что загрузил еще одно изображение, описывающее то, что я пытаюсь сделать.. это похоже на карту отношений сущностей с документом в системе.. пожалуйста, посмотрите и выскажи свое мнение, если есть время..
и я думаю, что единственный терминатор, о котором я думаю... начальная точка и конечная точка... которая является идентификатором базового документа, равна нулю... и второе завершение, если идентификатор целевого документа равен нулю. не уверен, но если я смогу управлять терминатором в обоих направлениях, я перенесу цепочку от начального документа к последнему документу.. потому что большинство или все из них могут иметь ссылки друг на друга..
Проверьте stackoverflow.com/questions/9650045/…