Необходимо создать функцию T-SQL для работы с таблицей, хранящей дерево?

Question

[purple_cat]

Добрый день. Имеется функция, которая возвращает список список идентификаторов всех
дочерних вершин заданной вершины. Однако необходимо, чтобы она возвращала данный список на глубину, не более заданной. Глубину нужно передавать в качестве параметра @level

ALTER FUNCTION GET_ALL_CHILDREN_LEVEL(@parent INT, @mode VARCHAR(50),@level INT)

RETURNS @all_children TABLE
(
id VARCHAR(max)
)
AS
BEGIN
DECLARE @level_new INT
SET @level_new=0
IF (@mode = 'TABLE')
BEGIN
	WITH [tree] ([sp_id], [sp_parent])
	AS
	(			
			SELECT [sp_id], [sp_parent]
			FROM [site_pages_new]
			WHERE [sp_id] = @parent 
			UNION ALL
			SELECT [inner].[sp_id], [inner].[sp_parent]
			FROM [site_pages_new] AS [inner]
			JOIN [tree]
			ON [inner].[sp_parent] = [tree].[sp_id]

	)

	INSERT @all_children
	SELECT CAST([sp_id] AS VARCHAR)
	FROM [tree]
	WHERE [sp_id] != @parent 


END
ELSE
	BEGIN
		WITH [tree] ([sp_id], [sp_parent])
		AS
		(
		SELECT [sp_id],
		[sp_parent]
		FROM [site_pages_new]
		WHERE [sp_id] = 2
		UNION ALL
		SELECT [inner].[sp_id],
		[inner].[sp_parent]
		FROM [site_pages] AS [inner]
		JOIN [tree]
		ON [inner].[sp_parent] = [tree].[sp_id]
		)
		INSERT @all_children
		SELECT STUFF((SELECT ',' + CAST([sp_id] AS VARCHAR)
		FROM [tree]
		WHERE [sp_id] != 2
		FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
	END;
RETURN
END;

Код таблицы, для которой должна быть написана функция

CREATE TABLE site_pages_new (
    sp_id INT NOT NULL,
    sp_parent INT NULL,
    sp_name NVARCHAR(200) NOT NULL,
	 PRIMARY KEY  (sp_id),
	 FOREIGN KEY (sp_parent) REFERENCES site_pages_new (sp_id)
	 -- ON UPDATE CASCADE 
	  --ON DELETE CASCADE
);
 
INSERT INTO site_pages_new VALUES
    (1, NULL, 'Главная'),
    (2, 1, 'Читатели'),
    (5, 2, 'Новости'),
	(6, 2, 'Статистика'),
	(12, 6, 'Текущая'),
	(13, 6, 'Архивная'),
	(14, 6, 'Неофициальная'),
	(10, 1, 'Контакты'),
	(3, 1, 'Спонсорам'),
	(7, 3, 'Предложения'),
	(8, 3, 'Истории успеха'),
	(11, 3, 'Документы'),
	(4, 1, 'Рекломадателям'),
	(9, 4, 'Акции');

Answer 1

[d-stream]

Стоит около шоссе толпа братков. Мимо них проезжает на авто мужик, останавливается и спрашивает:
- Я могу проехать по этой дороге в Солнечногорск?
Братва посовещалась немножко. Потом выходит из группы один и говорит:
- Слышь, мужик, братва не возражает.

В чем вопрос?

p.s. hint ms sql умеет recursive cte и в том числе управляется через maxrecursion
https://docs.microsoft.com/ru-ru/sql/t-sql/queries...

Comments

[purple_cat]

Пробую использовать такой код, но результат - пустая таблица, т.к. в @sp_level остается 0. Можете подсказать как исправить, чтобы @sp_level увеличивался?

DECLARE @sp_level INT
SET @sp_level=0;

WITH [tree] ([sp_id], [sp_parent], [sp_name], sp_level) 
		AS   
	(  
		SELECT [sp_id], [sp_parent], [sp_name], 0 AS sp_level 
		FROM [site_pages_new]
		WHERE [sp_id] = 1
		UNION ALL  
		SELECT e.[sp_id], e.[sp_parent], e.[sp_name], @sp_level + 1  
		FROM [site_pages_new] AS e  
			INNER JOIN [tree] AS d  
			ON e.[sp_id] = d.[sp_parent]   
	)  
	SELECT [sp_id], [sp_parent], [sp_name], @sp_level
	FROM [site_pages_new] 
	WHERE @sp_level <= 2

[d-stream]

удалить две первые строки и далее удалить три @

...

Answer 2

[unfilled]

внутри рекурсивного cte я бы добавил "счётчик" уровней, типа

WITH [tree] ([sp_id], [sp_parent], [lvl])
    AS
    (
    SELECT [sp_id],
    [sp_parent],
    1 as [lvl]
    FROM [site_pages_new]
    WHERE [sp_id] = 2
    UNION ALL
    SELECT [inner].[sp_id],
    [inner].[sp_parent],
    [tree].[lvl] + 1
    FROM [site_pages] AS [inner]
    JOIN [tree]
    ON [inner].[sp_parent] = [tree].[sp_id]
    WHERE [tree[.[lvl] < @level
    )

Ну а <= или < использовать - по месту надо смотреть, что вы и как передавать будете