set nocount on
declare @ table(xl int,zh int,xh int)
insert @ select 8,1,1
union all select 8,2,2
union all select 8,3,3
union all select 6,4,1
union all select 6,3,2
union all select 6,5,3
union all select 7,8,1
union all select 7,5,2
union all select 7,9,3
union all select 7,10,4
union all select 7,11,5
--select xl 线路,zh 站号,xh 序号 from @
--我给出开始站1,结束站9,求需要中转的站!
===================================================================
--我只能先假设你的公交车是来回的。就是非单程的。
declare @start int,@end int
set @start=1
set @end=9
select cast(rtrim(xl)+','+rtrim(zh) as varchar(1000)) as t,xl,zh into #t
from @
where zh=@start
insert #t select A.t+'到'+rtrim(B.xl)+','+rtrim(B.zh),B.xl,B.xh
from #t a,@ B
where a.zh<>b.zh and a.xl=b.xl
while not exists (select 1
from #t
where zh=@end)
and exists (select 1
from #t a,@ B
where a.zh=b.zh
and a.xl<>b.xl
and a.t+'转'+rtrim(B.xl)+','+rtrim(B.zh) not in (select t from #t)
and B.xl not in (select xl from #t))
begin
insert #t select A.t+'转'+rtrim(B.xl)+','+rtrim(B.zh),B.xl,B.zh
from #t a,@ B
where a.zh=b.zh
and a.xl<>b.xl
and a.t+'到'+rtrim(B.xl)+','+rtrim(B.zh) not in (select t from #t)
and B.xl not in (select xl from #t)
insert #t select A.t+'到'+rtrim(B.xl)+','+rtrim(B.zh),B.xl,B.zh
from #t a,@ B
where a.zh<>b.zh
and a.xl=b.xl
and not exists
(select 1 from #t where xl=b.xl and zh=b.zh)
end
select top 1 t from #t where zh=@end order by len(t)