Sql server обход дерева

CTE Recursion to get tree hierarchy

I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I’ve simplified the data for sake of example):

EstimateItemID EstimateID ParentEstimateItemID ItemType -------------- ---------- -------------------- -------- 1 A NULL product 2 A 1 product 3 A 2 service 4 A NULL product 5 A 4 product 6 A 5 service 7 A 1 service 8 A 4 product

A ___/ \___ / \ 1 4 / \ / \ 2 7* 5 8 / / 3* 6*

Using this query, I can get the hierarchy (just pretend ‘A’ is a uniqueidentifier, I know it isn’t in real life):

DECLARE @EstimateID uniqueidentifier SELECT @EstimateID = 'A' ;WITH temp as( SELECT * FROM EstimateItem WHERE EstimateID = @EstimateID UNION ALL SELECT ei.* FROM EstimateItem ei INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID ) SELECT * FROM temp 

EstimateItemID -------------- 1 2 3 4 5 6 7 8

Unfortunately, what I need is an ordered hierarchy with a result set that follows the following constraints:

1. each branch must be grouped 2. records with ItemType 'product' and parent are the top node 3. records with ItemType 'product' and non-NULL parent grouped after top node 4. records with ItemType 'service' are bottom node of a branch

EstimateItemID -------------- 1 2 3 7 4 5 8 6

3 Answers 3

;WITH items AS ( SELECT EstimateItemID, ItemType , 0 AS Level , CAST(EstimateItemID AS VARCHAR(255)) AS Path FROM EstimateItem WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID UNION ALL SELECT i.EstimateItemID, i.ItemType , Level + 1 , CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255)) FROM EstimateItem i INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID ) SELECT * FROM items ORDER BY Path 

With Path — rows a sorted by parents nodes

If you want sort childnodes by ItemType for each level, than you can play with Level and SUBSTRING of Path column.

Here SQLFiddle with sample of data

Brilliant. This is a few years old, but found it useful today. However, forgive for saying, I found the example provided in the original post hard for me to translate into a more common solution. So, I reposted your (great) idea using more common data, table name, and fields to make it easier for others to follow.

Using the code above in SQL server 2017 (and ssms17) I get the error «Invalid column name ‘level’ » for the second level used (Level + 1). Any idea how to fix it?

@Ali, you can convert Path column into HierarchyId before ordering. You need slightly modify the path so it can be converted. Example

This is an add-on to Fabio’s great idea from above. Like I said in my reply to his original post. I have re-posted his idea using more common data, table name, and fields to make it easier for others to follow.

Thank you Fabio! Great name by the way.

First some data to work with:

CREATE TABLE tblLocations (ID INT IDENTITY(1,1), Code VARCHAR(1), ParentID INT, Name VARCHAR(20)); INSERT INTO tblLocations (Code, ParentID, Name) VALUES ('A', NULL, 'West'), ('A', 1, 'WA'), ('A', 2, 'Seattle'), ('A', NULL, 'East'), ('A', 4, 'NY'), ('A', 5, 'New York'), ('A', 1, 'NV'), ('A', 7, 'Las Vegas'), ('A', 2, 'Vancouver'), ('A', 4, 'FL'), ('A', 5, 'Buffalo'), ('A', 1, 'CA'), ('A', 10, 'Miami'), ('A', 12, 'Los Angeles'), ('A', 7, 'Reno'), ('A', 12, 'San Francisco'), ('A', 10, 'Orlando'), ('A', 12, 'Sacramento'); 

-- Note: The 'Code' field isn't used, but you could add it to display more info. ;WITH MyCTE AS ( SELECT ID, Name, 0 AS TreeLevel, CAST(ID AS VARCHAR(255)) AS TreePath FROM tblLocations T1 WHERE ParentID IS NULL UNION ALL SELECT T2.ID, T2.Name, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath FROM tblLocations T2 INNER JOIN MyCTE itms ON itms.ID = T2.ParentID ) -- Note: The 'replicate' function is not needed. Added it to give a visual of the results. SELECT ID, Replicate('.', TreeLevel * 4)+Name 'Name', TreeLevel, TreePath FROM MyCTE ORDER BY TreePath; 

Источник

Рекурсия в MS SQL

Иногда, в хранимой процедуре или функции требуется использовать результаты выборки несколько раз. В таких случаях мы часто используем временные таблицы. Однако, стоит учитывать некоторые преимущества и недостатки временных таблиц. Преимущества:

• Временные таблицы являются полноценными таблицами. Поэтому для них можно создавать индексы и статистику. Это может существенно ускорить работу с ними.

• Заполнение временной таблицы связано с перемещением данных. Хоть это и простая операция Insert, все же при больших объемах данных есть нагрузка на диски;
• Существует риск увеличения времени выполнения запросов. Временные таблицы создаются в базе tempdb. А нагрузка на эту базу существенная.

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

Обобщённое табличное выражение

Common Table Expression (CTE) выражение с общей таблицей, которую можно использовать множество раз в запросе. CTE не сохраняет данные, а создает нечто вроде временного представления. Кто-то может сказать, что CTE – это подзапрос, который предшествует основному запросу. Но это не совсем так, ведь подзапрос нельзя использовать несколько раз, а CTE можно.

Когда же стоит использовать обобщенное табличное выражение?

1. Для создания рекурсивных запросов, с помощью которых можно получить данные в иерархическом виде;
2. При многократных ссылках на набор данных в пределах одного запроса;
3. С целью заменить представления, временные таблицы, табличные переменные.

К преимуществам CTE можно отнести: рекурсию, высокую скорость работы запроса, лаконичность запроса.

А к недостаткам отнесем ограниченность использования. CTE может использоваться только для запроса, к которому он принадлежит. Невозможно использовать его в других запросах. В этом случае придется использовать временные таблицы или табличные переменные.

Обобщенные табличные выражения бывают простые и рекурсивные.

Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.

Рекурсивные CTE используются для возвращения иерархических данных

Рассмотрим пример простого CTE предложения:

 WITH CTEQuery (Field1, Field2) AS ( SELECT (Field1, Field2) FROM TABLE ) SELECT * FROM CTEQuery

Field1, Field2 – имена полей запроса;

Table – некая таблица, из которой выбираются данные для использования в основном запросе.

В это примере можно и не указывать явно поля выборки, так как мы выбираем все поля из таблицы TestTable:

WITH CTEQuery AS ( SELECT * FROM Table ) SELECT * FROM CTEQuery

С помощью CTE можно оптимизировать основной запрос если вынести часть логики в CTE. Дело в том, что CTE позволяет создавать сразу несколько выражений (запросов). Таким образом вы можете разбить сложный запрос на несколько предварительных «представлений» с помощью CTE, а затем связать их в общем запросе:

1 2 3 4 5 6 7 8 9 10 11 12

WITH CTEQuery1 (Field1, Field2) AS ( SELECT Field1 AS ID, Field2 FROM Table1 WHERE Field2 >= 1000 ), CTEQuery2 (Field3, Field4) AS ( SELECT Field3 AS ID, Field4 FROM Table2 WHERE Field4 = 'Москва' ) SELECT * FROM CTEQuery1 INNER JOIN CTEQuery2 ON CTEQuery2.ID = CTEQuery1.ID

Как было сказано выше, основное назначение CTE – рекурсия. Типовая задача для рекурсии – обход дерева. Так что мы можем строить дерево с помощью with. Структура рекурсивного запроса впервые появилась в SQL Server 2005.

Взгляните на инструкцию WITH:

WITH RecursiveQuery AS ( Anchor> UNION ALL Joined TO RecursiveQuery> ) SELECT * FROM RecursiveQuery

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

После UNION ALL следует запрос к целевой таблице с JOIN к CTE выражению.

— SELECT из целевой таблицы. Обычно это та же таблица, которая используется в якоре. Но в этом запросе она соединяется с CTE выражением, образуя рекурсию. Условие этого соединения определяет отношение родитель – ребенок. От этого зависит переходите ли вы на верхние уровни дерева или на нижние.

Давайте посмотрим на рекурсивный запрос, который возвращает список подразделений организации. Подготовим данные для этого запроса:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

CREATE TABLE Department ( ID INT, ParentID INT, Name VARCHAR(50) ) INSERT INTO Department ( ID, ParentID, Name )  VALUES (1, 0, 'Finance Director') INSERT INTO Department ( ID, ParentID, Name )  VALUES (2, 1, 'Deputy Finance Director') INSERT INTO Department ( ID, ParentID, Name )  VALUES (3, 1, 'Assistance Finance Director') INSERT INTO Department ( ID, ParentID, Name )  VALUES (4, 3, 'Executive Bodget Office') INSERT INTO Department ( ID, ParentID, Name )  VALUES (5, 3, 'Comptroller') INSERT INTO Department ( ID, ParentID, Name )  VALUES (6, 3, 'Purchasing') INSERT INTO Department ( ID, ParentID, Name )  VALUES (7, 3, 'Debt Management') INSERT INTO Department ( ID, ParentID, Name )  VALUES (8, 3, 'Risk Management') INSERT INTO Department ( ID, ParentID, Name )  VALUES (9, 2, 'Public Relations') INSERT INTO Department ( ID, ParentID, Name )  VALUES (10, 2, 'Finance Personnel') INSERT INTO Department ( ID, ParentID, Name )  VALUES (11, 2, 'Finance Accounting') INSERT INTO Department ( ID, ParentID, Name )  VALUES (12, 2, 'Liasion to Boards and Commissions')

Уже сейчас понятно, что структура подразделений в организации иерархическая. Наша задача получить список подразделений, подчиненных помощнику финансового директора. Если рассуждать в контексте иерархического дерева, то мы должны найти ветвь и ее листья.

Но сначала давайте посмотрим весь список подразделений:

Источник

Как в T-SQL пройти по дереву?

У меня есть таблица, она представляет собой структуру файлов и папок. Там есть поля id, id_parent, file_name_cl. Некоторые записи ссылаются на другие записи этой же таблицы через id_parent (как файлы в папке). Так же у меня есть строка такого вида: «\Имя\Имя2\Имя3», это так сказать путь до конечного файла в таблице. Подскажите как мне написать запрос или функцию, чтобы передав ему такую строку, я смог получить искомый файл? Строку я могу разбить в массив имён, вне T-SQL и думаю передать в виде таблицы тоже, но как мне пройтись по дереву, слабо представляю. UPDATE: Получилось сделать вот в таком виде:

CREATE TYPE [dbo].[FullPathType] AS TABLE(idx int, Name nvarchar(1000) not null) GO CREATE function [dbo].[GetFileByFullPath] (@full_path dbo.FullPathType readonly) returns int begin DECLARE @i int DECLARE @count_rows int DECLARE @current_parent_id int DECLARE @current_name nvarchar(1000) SET @i = 1 SET @current_parent_id = null SET @count_rows = (SELECT COUNT(*) FROM @full_path) IF @count_rows > 0 while (@i<=(select max(idx) from @full_path)) begin SET @current_name = (SELECT Name FROM @full_path WHERE idx = @i) SET @current_parent_id = (select id from Files where file_name_cl=@current_name and id_parent=@current_parent_id) SET @i=@i+1 end RETURN @current_parent_id; end go 

Ну чему тут не получаться-то? строите рекурсивно от корня всё дерево (можно ограничиться уровнем, который у искомого ресурса), и соответственно полные пути узлов, и в полученном ищете заданный путь.

Источник