问题是这样。某个城市里的公共汽车路线。有几个集中的中转站。就是某些路线在同一个地方交汇。在交汇点乘客可以转车。已知各线路沿途停靠站名。若在程序中给定上车点和下车点如何能查询出在什么地方转车。转什么线路车。求解。
处理如下：
create table test(xl int,zh int,xh int)
insert test 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 test
线路 站号 序号
----------- ----------- -----------
8 1 1
8 2 2
8 3 3
6 4 1
6 3 2
6 5 3
7 8 1
7 5 2
7 9 3
7 10 4
7 11 5
declare @st int,@end int
set @st=2--起始
set @end=10--到达
select a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en into #temp from test a,test b where a.zh=@st and a.xl=b.xl and a.zh<>b.zh
while not exists(select 1 from #temp where en=@end)
insert #temp select a.st,b.en,a.st_en+','+b.st_en from #temp a,
(select a.xl,a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en from test a,test b where a.xl=b.xl and a.zh<>b.zh) b
where a.en=b.st and charindex(rtrim(b.xl)+'.',a.st_en)=0--and a.xh
1<b.xh #temp="" *="" -----------="" ---------------------------="" 10="" 2="" 8.2-8.3,6.3-6.5,7.5-7.10="" @end="10" @st="2" \-----------="" a,test="" a.xl="b.xl" a.zh="@st" a.zh<="" and="" as="" b="" declare="" drop="" en="" en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh)="" from="" int="" int,@end="" into="" num="" select="" set="" st="" st,b.zh="" st_en="" st_en,abs(a.xh-b.xh)="" table="" test="" where="" 考虑到可能有多种方式，选途经站点最少的。="">b.zh
2while not exists(select 1 from #temp where en=@end)
3insert #temp select a.st,b.en,a.st_en+','+b.st_en,a.num+b.num from #temp a,
4(select a.xl,a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en,abs(a.xh-b.xh) as num
5from test a,test b where a.xl=b.xl and a.zh<>b.zh) b
6where a.en=b.st and charindex(rtrim(b.xl)+'.',a.st_en)=0--and a.xh<b.xh
7
8select top 1 * from #temp where st=@st and en=@end order by num
9drop table #temp
10st en st_en num
11\----------- ----------- --------------------------------------------------- -----------
122 10 8.2-8.3,6.3-6.5,7.5-7.10 4</b.xh>