3.查询换乘路线算法

(1)直达路线视图

直达路线视图可以理解为一张存储了所有直达路线的表(如果两个站点之间存在直达路线，那么在直达路线视图中就有一行与之相对应)

create view RouteT0

as

select

sr1.Stop as StartStop,        --启始站点

sr2.Stop as EndStop,        --目的站点

sr1.Route as Route,        --乘坐线路

sr2.Position-sr1.Position as StopCount        --经过的站点数

from

stop_route sr1,

stop_route sr2

where

sr1.Route=sr2.Route

and sr1.Position<sr2.Position

(2)换乘路线算法

显然，一条换乘路线由若干段直达路线组成，因此，基于直达路线视图RouteT0可以很方便实现换乘查询，以下是实现一次换乘查询的存储过程InquiryT1：

create proc InquiryT1(@StartStop varchar(32),@EndStop varchar(32))

as

begin

select

r1.StartStop as 启始站点,

r1.Route as 乘坐路线1,

r1.EndStop as 中转站点,

r2.Route as 乘坐路线2,

r2.EndStop as 目的站点,

r1.StopCount+r2.StopCount as 总站点数

from

RouteT0 r1,

RouteT0 r2

where

r1.StartStop=@StartStop

and r1.EndStop=r2.StartStop

and r2.EndStop=@EndStop

end

同理可以得到二次换乘的查询语句

create proc InquiryT2(@StartStop varchar(32),@EndStop varchar(32))

as

begin

select

r1.StartStop as 启始站点,

r1.Route as 乘坐路线1,

r1.EndStop as 中转站点1,

r2.Route as 乘坐路线2,

r2.EndStop as 中转站点2,

r3.Route as 乘坐路线3,

r3.EndStop as 目的站点,

r1.StopCount+r2.StopCount+r3.StopCount as 总站点数

from

RouteT0 r1,

RouteT0 r2,

RouteT0 r3

where

r1.StartStop=@StartStop

and r1.EndStop=r2.StartStop

and r2.EndStop=r3.StartStop

and r3.EndStop=@EndStop

end

值得学习ing!

路过！！！！！！！