На оптимальность не претендую, получилось что-то вроде такого:
Таблица и заполнение:
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.
30.
31.
32.
33.
34.
35.
CREATE TABLE [dbo].[links](
[link_id] [int] IDENTITY(1,1) NOT NULL,
[node1_id] [int] NOT NULL,
[node2_id] [int] NOT NULL,
[weight] [int] NULL,
[id_min] AS (case when [node1_id]<[node2_id] then [node1_id] else [node2_id] end),
[id_max] AS (case when [node1_id]>[node2_id] then [node1_id] else [node2_id] end),
[descr] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[link_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[id_min] ASC,
[id_max] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into dbo.links
(node1_id, node2_id, weight, descr)
SELECT *
FROM (
VALUES
(1,6,14,NULL),
(1,3,9,NULL),
(1,2,7,NULL),
(6,5,9,NULL),
(6,3,2,NULL),
(3,4,11,NULL),
(3,2,10,NULL),
(2,4,15,NULL),
(4,5,6,NULL)) AS vtable
([node1_id],[node2_id],[weight],[descr])
Поиск:
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.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
declare
@step_id int = 2
,@max_recurtion int = 10
,@first_node int = 1
,@last_node int = 5
declare @t table (
deep int
,node1_id int
,node2_id int
,[root] nvarchar(max)
,[weight] int
,first_node int
,last_node as (case when charindex(',', [root]) > 0
then cast(substring(reverse([root]), 0, charindex(',', [root])) as integer)
else 0
end)
)
insert into @t
(
deep
,node1_id
,node2_id
,[root]
,[weight]
,first_node
)
select
1
,node1_id
,node2_id
,cast(node1_id as nvarchar(8)) + ',' + cast(node2_id as nvarchar(8))
,l.[weight]
,l.node1_id
from
links l
while @step_id < @max_recurtion
begin
insert into @t
(
deep
,node1_id
,node2_id
,[root]
,[weight]
,first_node
)
select
@step_id
,t.node2_id
,l.node2_id
,t.[root] + ',' + cast(l.node2_id as nvarchar(8))
,t.[weight] + l.[weight]
,t.first_node
from
@t t
inner join links l
on t.node2_id = l.node1_id
and l.node2_id <> t.node1_id
where
t.deep = @step_id - 1
if @@rowcount = 0
break;
set @step_id = @step_id + 1
end
select
*
from
@t t
where t.first_node = @first_node and t.last_node = @last_node
or t.first_node = @last_node and t.last_node = @first_node
|
