Рекурсивный иерархический запрос SQL Server, менее чем оптимальная структура данных

Если у вас есть:

(документ, родитель) (документ, прародитель) ... (документ, предок) (документ, корень) (родитель, прародитель) ... (родитель, предок) (родитель, корень) ... (прародитель, прапрародитель ) ... (прародитель, корень)

Вы хотите ограничить свою рекурсию только:

(документ, родитель)(родитель, дедушка и бабушка)(прародитель, прадедушка) (прадедушка, корень)

Правильно? Таким образом, хитрость заключается в том, чтобы просто выяснить, какие условия поставить в нижнюю половину вашего рекурсивного cte, чтобы не ВСЕ родственники текущей строки присоединялись к текущей строке.

Итак, для document вам нужно parent. Для parent вам нужно grandparent и так далее. Как мы можем создать этот фильтр?

Итак, для document что верно в отношении parent, чего нет в отношении любого другого предка?

Ответ: document имеет всех тех же родственников, что и parent, но только document имеет предком parent.

Итак, когда вы смотрите на родителей для каждого элемента папки, если существует другая строка, в которой родитель этого родителя совпадает с одним из родителей текущего элемента, тогда этот элемент папки не является непосредственным родителем текущего элемента папки и должен быть отфильтрован из рекурсивное соединение.

Представьте себе интервью: «Итак, вы один из моих предков. Если ни у кого из других моих предков вы также не являетесь предком, то вы должны быть моим непосредственным родителем».

Правда, нет?

У меня нет вашей структуры для тестирования, но я думаю, что добавление чего-то подобного в ваше рекурсивное соединение сработает, но может потребоваться некоторая настройка...

{for recursion -- Join second half of UNION back to main CTE}
ON bottom.Folder_Item=cte.parent  --you are my ancestor
AND NOT EXISTS(
 SELECT * FROM MyTable mt --others
 WHERE mt.folder_item<>bottom.folder_item --the other is not you
 AND mt.Folder_Item=cte.parent --the other is also my ancestor
 AND mt.Parent=bottom.folder_item --the other also has you as ancestor
)

Для этого фрагмента bottom — это мой псевдоним для нижней половины вашего рекурсивного UNION, а cte — это псевдоним всего CTE.

И, поместив этот фильтр в условие JOIN для рекурсии, я думаю, это должно предотвратить схождение дерева рекурсии с ума и превышение предела рекурсии. Я думаю!

Если вы по-прежнему получаете предел рекурсии, попробуйте предварительно отфильтровать свою таблицу таким образом в CTE перед рекурсивным CTE, чтобы вы предварительно исключили всех не непосредственных предков перед выполнением рекурсии.

Я думаю, что это должно работать, но если нет, вы определенно можете делать то, что вам нужно, с помощью КУРСОРА.