公交车路线查询系统后台数据库设计-引入步行路线

在《查询算法》和《关联地名和站点》两篇文章中，已经实现了通过地名或站点进行路线查询的算法，但是在现实中，从起点到终点不一定全程都是乘车，例如，有以下3条路线：

R1: S1->S2->S3->S4->S5

R2: S6->S7->S2->S8

R3: S8->S9->S10

假如现在要从站点S1到S7，如果用Inquiry查询路线，显然没有合适的乘车方案。但是S2和S7相距仅仅一个站的距离，可以用步行代替，因此可以先从S1乘坐R1到S2再步行到S7。

为了实现在乘车路线中插入步行路线，在数据库使用WalkRoute(StartStop, EndStop, Distance, Remark)(StartStop-起始站点,EndStop-目的站点,Distance-距离,Remark-备注)储存距离较近的两个站点。

加入表WalkRoute后，查询算法也要作相应的修改，其实WalkRoute和RouteT0很相似，因此只需把WalkRoute看成是特殊的直达线路即可，修改后的InqueryT1如下： 

/* 
   查询站点@StartStops到站点@EndStops之间的一次换乘乘车路线，多个站点用'/'分开，如： 
   exec InquiryT1 '站点1/站点2','站点3/站点4' 
 */ 
CREATE proc InquiryT1(@StartStops varchar(32),@EndStops varchar(32)) 
as 
begin 
	declare @ss_tab table(name varchar(32)) 
	declare @es_tab table(name varchar(32)) 
	insert @ss_tab select Value from dbo.SplitString(@StartStops,'/') 
	insert @es_tab select Value from dbo.SplitString(@EndStops,'/') 
	if(exists(select * from @ss_tab sst,@es_tab est where sst.name=est.name)) 
	begin 
		raiserror ('起点集和终点集中含有相同的站点',16,1) 
		return 
	end 
	declare @stops table(name varchar(32)) 
	insert @stops select name from @ss_tab 
	insert @stops select name from @es_tab 
	declare @result table( 
		StartStop varchar(32), 
		Route1 varchar(256), 
		TransStop varchar(32), 
		Route2 varchar(256), 
		EndStop varchar(32), 
		StopCount int 
	) 
	declare @count int 
	set @count=0 
	--查询"步行-乘车"路线 
	insert @result 
	select 
		sst.name as StartStop, 
		'从'+r1.StartStop+'步行到'+r1.EndStop as Route1, 
		r1.EndStop as TransStop, 
		r2.Route as Route2, 
		est.name as EndStop, 
		r2.StopCount as StopCount 
	from 
		@ss_tab sst, 
		@es_tab est, 
		(select * from WalkRoute where EndStop not in (select name from @stops)) r1, 
		RouteT0 r2 
	where 
		sst.name=r1.StartStop 
		and r1.EndStop=r2.StartStop 
		and r2.EndStop=est.name 
		order by r2.StopCount 
		set @count=@@rowcount 
	--查询"乘车-步行"路线 
	insert @result 
	select 
		sst.name as StartStop, 
		r1.Route as Route1, 
		r1.EndStop as TransStop, 
		'从'+r2.StartStop+'步行到'+r2.EndStop as Route2, 
		est.name as EndStop, 
		r1.StopCount as StopCount 
	from 
		@ss_tab sst, 
		@es_tab est, 
		RouteT0 r1, 
	(select * from WalkRoute where StartStop not in (select name from @stops)) r2 
	where 
		sst.name=r1.StartStop 
		and r1.EndStop=r2.StartStop 
		and r2.EndStop=est.name 
		order by r1.StopCount 
	set @count=@count+@@rowcount 
	
	if(@count=0) 
	begin 
	--查询"乘车-乘车"路线 
	insert @result 
	select 
		sst.name as StartStop, 
		r1.Route as Route1, 
		r1.EndStop as TransStop, 
		r2.Route as Route2, 
		est.name as EndStop, 
		r1.StopCount+r2.StopCount as StopCount 
	from 
		@ss_tab sst, 
		@es_tab est, 
		(select * from RouteT0 where EndStop not in (select name from @stops)) r1, 
		RouteT0 r2 
	where 
		sst.name=r1.StartStop 
		and r1.EndStop=r2.StartStop 
		and r2.EndStop=est.name 
		and r1.Route<>r2.Route 
		order by r1.StopCount+r2.StopCount 
	end 
	select 
		StartStop as 起始站点, 
		Route1 as 路线1, 
		TransStop as 中转站点, 
		Route2 as 路线2, 
		EndStop as 目的站点, 
		StopCount as 总站点数 
	from 
	@result 
end