SELECT * FROM Vzakladke.net

Статьи об автоматизации и программировании

Рекурсивный запрос на MS SQL с использованием обобщенного табличного выражения (CTE)

 

 

Рекурсивный SQL запрос - один из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку.
В MS SQL нет ключевого слова recursive, но рекурсию можно выполнить используя создание простого обобщенного табличного выражения CTE (с использованием ключевого слова WITH). С его помощью можно написать рекурсивный запрос, т.е. запрос, который, написанный один раз, будет повторяться многократно пока истинно некоторое условие.


Рекурсивный CTE имеет следующий вид:

WITH <имя>[(<список столбцов>)]
AS(
< SELECT... > -- анкорная часть
UNION ALL -- рекурсивная часть
< SELECT...FROM <имя>… > 
WHERE <условие продолжения итераций>
)
 

От обычного WITH CTE-запроса рекурсивный отличается только рекурсивной частью, которая вводится предложением UNION ALL. Обратите внимание, что в рекурсивной части присутствует ссылка на имя CTE, т.е. внутри CTE ссылается само на себя. Это, собственно, и есть рекурсия. Естественно, анкорная и рекурсивная части должны иметь одинаковый набор столбцов.

 

Проведем небольшой тест, для этого в нижеприведенном примере объявим временную таблицу и наполним ее тестовыми данными (вместо CTE будем использовать слово Rec, чтобы читателю было понятно что CTE не является ключевым словом, а лишь посняет суть).

 

DECLARE  @TableVar  TABLE
(
       tree_id INT,     
       parent_id INT, 
       name VARCHAR(32)
)
 
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (1, 0, 'test1')
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (2, 1, 'test2')
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (3, 2, 'test3')
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (4, 1, 'test4')
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (5, 2, 'test5')
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (6, 5, 'test6')
INSERT INTO @TableVar ( tree_id, parent_id, name )  
VALUES (7, 1, 'test7')

-- SELECT * FROM @TableVar 

declare @node as   INTEGER;
Set @node = 1; -- устанавливаем начальную ноду, т.е. 1-ый идентификатор
WITH Rec(tree_id, parent_id, name, lvl)
AS
(
	SELECT tree_id, parent_id, name, 0 as lvl
	FROM @TableVar  e
	WHERE e.tree_id = @node -- здесь задаётся параметр
	UNION ALL
	SELECT e.tree_id, e.parent_id, e.name, r.lvl + 1 as lvl 
	FROM @TableVar  e
		JOIN Rec r ON e.parent_id = r.tree_id
)
SELECT tree_id, parent_id, name, lvl, replicate(char(160), 2*lvl) + name as brench 
FROM Rec
ORDER BY tree_id, parent_id

 

Получаем такой результат:

 

sql рекурсия

 

Продолжение статьи.

Попробуем теперь научиться считать по иерархии сумму элементов.

 

Код SQL:


  -- Hierarchical Cumulative Sum Using SQL Server --

DECLARE @ProductCategories TABLE (
    Id int IDENTITY(1,1) NOT NULL,  
    Amount float NOT NULL,  
    ProductCategoryId int NULL,  
    Name VARCHAR(150) NULL
)
  
INSERT @ProductCategories VALUES (100, NULL, N'A1')  
INSERT @ProductCategories VALUES (90, NULL, N'A2')  
INSERT @ProductCategories VALUES (80, NULL, N'A3')  
INSERT @ProductCategories VALUES (20, 1, N'A11')  
INSERT @ProductCategories VALUES (30, 1, N'A12')  
INSERT @ProductCategories VALUES (10, 1, N'A13')  
INSERT @ProductCategories VALUES (70, 2, N'A21')  
INSERT @ProductCategories VALUES (50, 2, N'A22')  
INSERT @ProductCategories VALUES (5, 4, N'A11.1')  
INSERT @ProductCategories VALUES (10, 4, N'A11.2')  
INSERT @ProductCategories VALUES (15, 5, N'A12.1')  
INSERT @ProductCategories VALUES (20, 5, N'A12.2')  
INSERT @ProductCategories VALUES (25, 9, N'A11.1.1')  
INSERT @ProductCategories VALUES (30, 9, N'A11.1.2')  
INSERT @ProductCategories VALUES (35, 10, N'A11.2.1')  
INSERT @ProductCategories VALUES (40, 10, N'A11.2.2') 

 
;WITH C AS  
(  
  SELECT T.id,  
         T.Amount,  
         T.id AS RootID  
  FROM @ProductCategories T  
  UNION ALL  
  SELECT T.id,  
         T.Amount,  
         C.RootID  
  FROM @ProductCategories T  
    INNER JOIN C   
      ON T.ProductCategoryId = C.id  
)  
  
SELECT T.id,  
       T.ProductCategoryId,  
       T.Name,  
       T.Amount,  
       S.AmountIncludingChildren  
FROM @ProductCategories T  
  INNER JOIN (  
             SELECT RootID,  
                    SUM(Amount) AS AmountIncludingChildren  
             FROM C  
             GROUP BY RootID  
             ) AS S  
    ON T.id = S.RootID  
ORDER by T.id  
OPTION (MAXRECURSION 0)  

 

cumulative sum

 

 

Дата публикации: 2018-05-22 11:12:22

MS SQL

1

Отзывы:

Хомяк
Проще было бы сделать в тестовом примере автоинкрементное поле так: tree_id int not null primary key identity(1,1)
Костантин
Кстати, данный алгоритм хорошо работает не только с целыми числами но и со строковыми типами данных. Вот пример (а на выходе получим почти тоже самое): DECLARE @TableVar TABLE ( tree_id VARCHAR(32), parent_id VARCHAR(32), name VARCHAR(32) ) INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x1', 'y0', 'test1') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x2', 'x1', 'test2') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x3', 'x2', 'test3') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x4', 'x1', 'test4') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x5', 'x2', 'test5') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x6', 'x5', 'test6') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES ('x7', 'x1', 'test7') declare @node as VARCHAR(32); Set @node = 'x1'; WITH Rec(tree_id, parent_id, name, lvl) AS ( SELECT tree_id, parent_id, name, 0 as lvl FROM @TableVar e WHERE e.tree_id = @node UNION ALL SELECT e.tree_id, e.parent_id, e.name, r.lvl + 1 as lvl FROM @TableVar e JOIN Rec r ON e.parent_id = r.tree_id ) SELECT tree_id, parent_id, name, lvl, replicate(char(160), 2*lvl) + name as brench FROM Rec ORDER BY tree_id, parent_id

Ваше имя:

Ваш e-mail (необязательно):

Сообщение:

Captcha