@purple_cat

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

Добрый день. Имеется функция, которая возвращает список список идентификаторов всех
дочерних вершин заданной вершины. Однако необходимо, чтобы она возвращала данный список на глубину, не более заданной. Глубину нужно передавать в качестве параметра @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, 'Акции');
  • Вопрос задан
  • 37 просмотров
Пригласить эксперта
Ответы на вопрос 2
@d-stream
Готовые решения - не подаю, но...
Стоит около шоссе толпа братков. Мимо них проезжает на авто мужик, останавливается и спрашивает:
- Я могу проехать по этой дороге в Солнечногорск?
Братва посовещалась немножко. Потом выходит из группы один и говорит:
- Слышь, мужик, братва не возражает.

В чем вопрос?

p.s. hint ms sql умеет recursive cte и в том числе управляется через maxrecursion
https://docs.microsoft.com/ru-ru/sql/t-sql/queries...
Ответ написан
unfilled
@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
    )

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

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы