求一个查询sql．谢谢

[web863]

UserID FriendID
1 2
1 3
3 5
5 2

.........
.......
从1出发找到5的线路就有：1-2-5和1-3-5
如何查找．
相当于无线图．从一个顶点到另一个顶点的所有线路

[w75251455]

--唉~做了没人看~~...
create table tb1(UserID int, FriendID int)
insert into tb1 select 1, 2
union all select 1, 3
union all select 3, 5
union all select 5, 2
union all select 3,4
union all select 4,5
union all select 1,6
union all select 2,6
union all select 4,1
union all select 1,7
union all select 7,8
union all select 9,8

declare @int int
set @int = 1

select @int'phint',
'1-'+cast((select userid from(select UserID union all select friendid)a where userid<>1)as varchar(10))'ph'
into #tb2
from tb1 where userid =1 or friendid=1

while(@@rowcount>0)
begin
set @int=@int+1
insert into #tb2
select @int,* from(
select ph+'-'+(
case when RIGHT(tb2.ph,1)=userid then cast(friendid as varchar(10))
when CHARINDEX(cast(userid as varchar(10)),ph)=0 then cast(userid as varchar(10)) end)ph
from (select * from #tb2 where phint=@int-1 and RIGHT(ph,1)<>5)tb2 left join tb1
on tb1.userid=RIGHT(tb2.ph,1) or tb1.friendid=RIGHT(tb2.ph,1)
)a
where CHARINDEX(RIGHT(ph,1),ph)=len(ph) and ph is not null
end

select * from #tb2 where substring(ph,len(ph),1)=5


drop table #tb2
/*
phint ph
----------- ------------
2 1-2-5
2 1-3-5
2 1-4-5
3 1-3-4-5
3 1-6-2-5
3 1-4-3-5
*/

[w75251455]

--唉~做了没人看~~...分又少~~太没意思了