会python真的可以为所欲为0(回)
331天前
这里还有人吗1(回)
517天前这里还有人吗0(回)
517天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
741天前到处都是羊，不想上班0(回)
821天前
鸽子
0(回)
825天前1、数据库设计 |
stop_route(站点,路线名,站点在路线中的位置) |
例如：如果有以下3条路线 |
R1: S1->S2->S3->S6->S4 |
则对应的关系表stop_route为 |
Stop Route Position |
S1 R1 1 |
S2 R1 2 |
S3 R1 3 |
S6 R1 4 |
S4 R1 5 |
2.直达乘车路线查询算法 |
基于表stop_route可以很方便实现直达乘车路线的查询，以下是用于查询直达乘车路线的存储过程InquiryT0： |
create proc InquiryT0(@StartStop varchar(32),@EndStop varchar(32)) |
as |
begin |
select |
sr1.Stop as 启始站点, |
sr2.Stop as 目的站点, |
sr1.Route as 乘坐线路, |
sr2.Position-sr1.Position as 经过的站点数 |
from |
stop_route sr1, |
stop_route sr2 |
where |
sr1.Route=sr2.Route |
and sr1.Position<sr2.Position |
and sr1.Stop=@StartStop |
and sr2.Stop=@EndStop |
end |
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 |
