Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 выделить path идущий от начала  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
Есть такой набор иерархических данных. начальное значение имеет parentid = 0

CREATE TABLE department
(
	id          int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name        nvarchar(255) NOT NULL,
	parentid    int NOT NULL,
    CONSTRAINT ux_department UNIQUE NONCLUSTERED (	name, parentid )
)
GO

CREATE NONCLUSTERED INDEX idx_department_idparent ON department ( parentid )
GO

INSERT INTO department (name, parentid) values ('root', 0)
INSERT INTO department (name, parentid) values ('child', (select id from department where name = 'root' and parentid = 0))
INSERT INTO department (name, parentid) values ('grandchild', (select id from department where name = 'child' and parentid = (select id from department where name = 'root' and parentid = 0)))

INSERT INTO department (name, parentid) values ('dad', 0)
INSERT INTO department (name, parentid) values ('son', (select id from department where name = 'dad' and parentid = 0))
INSERT INTO department (name, parentid) values ('grandson', (select id from department where name = 'son' and parentid = (select id from department where name = 'dad' and parentid = 0)))

INSERT INTO department (name, parentid) values ('father', 0)
INSERT INTO department (name, parentid) values ('son', (select id from department where name = 'father' and parentid = 0))
INSERT INTO department (name, parentid) values ('grandson', (select id from department where name = 'son' and parentid = (select id from department where name = 'father' and parentid = 0)))

INSERT INTO department (name, parentid) values ('grandfather', 0)
INSERT INTO department (name, parentid) values ('father', (select id from department where name = 'grandfather' and parentid = 0))
INSERT INTO department (name, parentid) values ('son', (select id from department where name = 'father' and parentid = (select id from department where name = 'grandfather' and parentid = 0)))
INSERT INTO department (name, parentid) values ('grandson', (select id from department where name = 'son' and parentid = (select id from department where name = 'father' and parentid = (select id from department where name = 'grandfather' and parentid = 0))))
GO


Можно ли найти пусть запросом от парсера?

;WITH cte AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) id, value FROM STRING_SPLIT('father\son\grandson', '\') x CROSS APPLY (SELECT id, name, parentid FROM department WHERE name = x.value)y
)SELECT * FROM cte JOIN department d ON cte.value = name



Или проще построить путь для каждого значения в таблице и сравнивать с эталоном?
19 июн 19, 19:50    [21911833]     Ответить | Цитировать Сообщить модератору
 Re: выделить path идущий от начала  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 323
WITH 
cte_src (path_el,N)
AS
(
	SELECT	VALUE,
			ROW_NUMBER() OVER (ORDER BY (SELECT 1))
	FROM string_split('father\son\grandson','\')
)
,cte_rec (id,[name],lvl)
AS
(
	SELECT  d.id,
			d.name,
			1
	FROM dbo.department AS d
		INNER JOIN cte_src AS cs
		 ON cs.N = 1
			AND cs.path_el = d.[name]
	WHERE d.parentid = 0
	
	UNION ALL
	
	SELECT  d.id,
			d.name,
			c.lvl + 1
	FROM cte_rec AS c
		INNER JOIN dbo.department AS d
			ON d.parentid = c.id
		INNER JOIN cte_src AS cs
			ON cs.N = c.lvl + 1
	WHERE d.[name] = cs.path_el			
)
SELECT *
FROM cte_rec
20 июн 19, 08:34    [21911989]     Ответить | Цитировать Сообщить модератору
 Re: выделить path идущий от начала  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
Cristiano_Rivaldo,

Спасибо, так или иначе второй вариант.
20 июн 19, 16:04    [21912326]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить