create table tb(souid varchar(10),destid varchar(10),lengh int)
insert tb select 'a','b',3
union all select 'a','v',4
union all select 'b','c',6
union all select 'c','g',8
union all select 'g','v',6
go
--查询的存储过程
create proc p_qry
@souid varchar(10)
as
set nocount on
declare @l int
set @l=1
create table #(souid varchar(10),lengh int,level int,path varchar(8000))
insert # select destid,lengh,@l,@souid+'->'+destid
from tb
where souid=@souid
while @@rowcount>0
begin
set @l=@l+1
insert # select a.destid,a.lengh+b.lengh,@l,b.path+'->'+a.destid
from tb a,# b
where a.souid=b.souid and b.level=@l-1
end
select * from #
go
--调用
exec p_qry 'a'
go
--删除测试
drop table tb
drop proc p_qry
/*--结果
souid lengh level path
---------- ----------- ----------- ------------------
b 3 1 a->b
v 4 1 a->v
c 9 2 a->b->c
g 17 3 a->b->c->g
v 23 4 a->b->c->g->v
--*/