--豢豌: https://www.youtube.com/watch?v=tZu4x5825LI
--drop table #edge
create table #edge(
from_point nvarchar(20)
, to_point nvarchar(20)
, distance int
)
go
insert into #edge values
(N'讌', N'覩語', 5)
, (N'讌', N'朱貅', 10)
, (N'讌', N'危', 9)
, (N'覩語', N'讌', 5)
, (N'覩語', N'朱貅', 3)
, (N'覩語', N'', 11)
, (N'朱貅', N'讌', 10)
, (N'朱貅', N'覩語', 3)
, (N'朱貅', N'ろ', 3)
, (N'朱貅', N'', 10)
, (N'朱貅', N'危', 7)
, (N'危', N'讌', 9)
, (N'危', N'朱貅', 7)
, (N'危', N'', 7)
, (N'危', N'蟲', 12)
, (N'ろ', N'朱貅', 3)
, (N'ろ', N'', 4)
, (N'', N'ろ', 4)
, (N'', N'朱貅', 10)
, (N'', N'覩語', 11)
, (N'', N'危', 7)
, (N'', N'蟲', 2)
, (N'蟲', N'', 2)
, (N'蟲', N'危', 12)
go
--讌 蟲襦 螳 豕蟆暑谿剰鍵
declare
@start_point nvarchar(20)
, @end_point nvarchar(20)
set @start_point = N'讌'
set @end_point = N'蟲'
;with cte
as
(
select
from_point
, to_point
, distance
, distance distance_sum
, 1 step
, convert(nvarchar(4000), from_point) path
from #edge
where from_point = @start_point
union all
select
a.from_point
, a.to_point
, a.distance
, a.distance + b.distance_sum
, b.step + 1 step
, convert(nvarchar(4000), b.path + '->' + a.from_point)
from #edge a
inner join cte b
on a.from_point = b.to_point
and b.path not like '%' + b.to_point + '%'
where 1=1
and a.from_point <> @end_point
)
select
path + '->' + to_point path
, distance_sum
, step
from cte
where to_point = @end_point
order by distance_sum
option (maxrecursion 0);
