3.二次查询算法
以下是二次换乘查询的存储过程 GInquiryT2 的代码,该存储过程使用了临时表来提高查
询效率:
GInquiryT2
/*
查询站点@StartStops 到站点@EndStops 之间的二次换乘乘车路线,多个站点用'/'分开,结果以分
组方式给出,如:
exec InquiryT2 '站点 1/站点 2','站点 3/站点 4'
*/
CREATE proc GInquiryT2(
@StartStops varchar(2048),
@EndStops varchar(2048)
)
as
begin
declare @ss_tab table(StopKey int)
declare @es_tab table(StopKey int)
insert @ss_tab
select distinct Stop.StopKey
from dbo.SplitString(@StartStops,'/') sn,Stop
where sn.Value=Stop.StopName
insert @es_tab
select distinct Stop.StopKey
from dbo.SplitString(@EndStops,'/') sn,Stop
where sn.Value=Stop.StopName
if(exists(select top 1 * from @ss_tab sst,@es_tab est where
sst.StopKey=est.StopKey))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
declare @stops table(StopKey int)
insert @stops select StopKey from @ss_tab
insert @stops select StopKey from @es_tab
print '===================================================='
print '筛选出第 1 段乘车路线'
print '----------------------------------------------------'
set statistics time on
------------------------------------------------------------