|
выделить path идущий от начала
#39828554
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: glubinka
Сообщения: 4 167
|
|
Есть такой набор иерархических данных. начальное значение имеет parentid = 0
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.
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
Можно ли найти пусть запросом от парсера?
1.
2.
3.
4.
;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
Или проще построить путь для каждого значения в таблице и сравнивать с эталоном? |
|
|
