Как создать рекурсивный запрос в MSSQL 2005?

Скажем, у меня есть следующая таблица:

CustomerID ParentID Name ========== ======== ==== 1 null John 2 1 James 3 2 Jenna 4 3 Jennifer 5 3 Peter 6 5 Alice 7 5 Steve 8 1 Larry 

Я хочу получить в одном запросе всех потомков Джеймса (Дженна, Дженнифер, Питер, Алиса, Стив). Спасибо, Пабло.

В SQL Server 2005 вы можете использовать CTE (Common Table Expressions) :

 with Hierachy(CustomerID, ParentID, Name, Level) as ( select CustomerID, ParentID, Name, 0 as Level from Customers c where c.CustomerID = 2 -- insert parameter here union all select c.CustomerID, c.ParentID, c.Name, ch.Level + 1 from Customers c inner join Hierachy ch on c.ParentId = ch.CustomerID ) select CustomerID, ParentID, Name from Hierachy where Level > 0 

Для получения снизу используйте ответ mathieu с небольшой модификацией:

 with Hierachy(CustomerID, ParentID, Name, Level) as ( select CustomerID, ParentID, Name, 0 as Level from Customers c where c.CustomerID = 2 -- insert parameter here union all select c.CustomerID, c.ParentID, c.Name, ch.Level + 1 from Customers c inner join Hierachy ch -- EDITED HERE -- on ch.ParentId = c.CustomerID ----------------- ) select CustomerID, ParentID, Name from Hierachy where Level > 0 

Вы не можете выполнять рекурсию в SQL без хранимых процедур. Способ решения этой проблемы – использование вложенных наборов, они в основном моделируют дерево в SQL как набор.

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

Пример Postgresql (с использованием очень немногих расширений postgresql, только SERIAL и ON COMMIT DROP, большинство RDBMS будут иметь схожие функции):

Настроить:

 CREATE TABLE objects( id SERIAL PRIMARY KEY, name TEXT, lft INT, rgt INT ); INSERT INTO objects(name, lft, rgt) VALUES('The root of the tree', 1, 2); 

Добавление ребенка:

 START TRANSACTION; -- postgresql doesn't support variables so we create a temporary table that -- gets deleted after the transaction has finished. CREATE TEMP TABLE left_tmp( lft INT ) ON COMMIT DROP; -- not standard sql -- store the left of the parent for later use INSERT INTO left_tmp (lft) VALUES((SELECT lft FROM objects WHERE name = 'The parent of the newly inserted node')); -- move all the children already in the set to the right -- to make room for the new child UPDATE objects SET rgt = rgt + 2 WHERE rgt > (SELECT lft FROM left_tmp LIMIT 1); UPDATE objects SET lft = lft + 2 WHERE lft > (SELECT lft FROM left_tmp LIMIT 1); -- insert the new child INSERT INTO objects(name, lft, rgt) VALUES( 'The name of the newly inserted node', (SELECT lft + 1 FROM left_tmp LIMIT 1), (SELECT lft + 2 FROM left_tmp LIMIT 1) ); COMMIT; 

Показывать дорожку снизу вверх:

 SELECT parent.id, parent.lft FROM objects AS current_node INNER JOIN objects AS parent ON current_node.lft BETWEEN parent.lft AND parent.rgt WHERE current_node.name = 'The name of the deepest child' ORDER BY parent.lft; 

Отображение всего дерева:

 SELECT REPEAT(' ', CAST((COUNT(parent.id) - 1) AS INT)) || '- ' || current_node.name AS indented_name FROM objects current_node INNER JOIN objects parent ON current_node.lft BETWEEN parent.lft AND parent.rgt GROUP BY current_node.name, current_node.lft ORDER BY current_node.lft; 

Выделите все из определенного элемента дерева:

 SELECT current_node.name AS node_name FROM objects current_node INNER JOIN objects parent ON current_node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = 'child' GROUP BY current_node.name, current_node.lft ORDER BY current_node.lft; 

Если я чего-то не хватает, рекурсия не нужна …

 SELECT d.NAME FROM Customers As d INNER JOIN Customers As p ON p.CustomerID = d.ParentID WHERE p.Name = 'James' 
  • Невозможно создать объекты с привязкой db; ORA - 0251: другой сеанс или ветвь в одной транзакции не завершена или завершена
  • Как предоставить пользователю только разрешение на доступ к базе данных
  • Как вставить данные из одной таблицы в другую в цикле с учетом производительности?
  • от многих до многих запросов
  • Процедура / функция SQL Server для копирования таблицы в другую базу данных
  • Каков самый быстрый способ сортировки запроса EF-to-Linq?
  • Возможно ли иметь две базы данных, которые используют одни и те же файлы?
  • SQL, чтобы получить количество рабочих дней между двумя датами
  • Длительность запросов SQL больше для меньшего набора данных?
  • Где я могу получить список почтовых индексов США с латикратными и долготными геокодами?
  • Как заставить индексы SQL Server занимать меньше места?
  • Давайте будем гением компьютера.