简单说一下我是怎么实现转乘的搜索的吧.
表结构如下(为方便大家看,我简化了一下,只把主要的字段列出来)
--公交线路表
CREATE TABLE [bus] (
[bus_id] [int] IDENTITY (1, 1) NOT NULL ,
[bus_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
)
--公交站表
CREATE TABLE [bus_stop] (
[stop_id] [int] IDENTITY (1, 1) NOT NULL ,
[stop_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
)
--公交线路与公交站的对应表
CREATE TABLE [bus_stops] (
[stops_id] [int] IDENTITY (1, 1) NOT NULL ,
[bus_id] [int] NOT NULL ,
[stop_id] [int] NOT NULL ,
[stop_order] [int] NULL ,
) ON [PRIMARY]
GO
定义了一个函数 dbo.getStopCount() 来取得某线路的两个站间隔的站点数.
站点1 站点2
先找到经过站点1的所有线路与这些线路经过的所有站点,再找到经过站点2的所有线路
与这些线路经过的所有站点,转乘站肯定要在这些站点中产生了.然后计算这些站点作为
转乘站时与站点1,站点2间隔的站点数的和,然后按小到大排序.
declare @stop_id int
declare @stop_id2 int
select @stop_id = 1
select @stop_id2 = 100
select a.bus_id,a.StopCount as stop_count1,a.stop_id,b.bus_id as bus_id2,
b.stopcount as stop_count2,a.StopCount + b.StopCount as stop_count
from
(select bus_id,stop_id,dbo.getStopCount(bus_id,stop_id,@stop_id) as StopCount from bus_stops
where bus_id in (select bus_id from bus_stops where stop_id = @stop_id))
and stop_id in (select stop_id from bus_stops where bus_id in
(select bus_id from bus_stops where stop_id = @stop_id2))) a
inner join
(select bus_id,stop_id,dbo.getStopCount(bus_id,stop_id,@stop_id2) as StopCount from bus_stops
where bus_id in (select bus_id from bus_stops where stop_id = @stop_id2)
and stop_id in (select stop_id from bus_stops where bus_id in
(select bus_id from bus_stops where stop_id = @stop_id))) b
on a.stop_id = b.stop_id
order by stop_count
]后台数据的处理是用 delphi7 做的，谁让咱 delphi 用得顺手呢。[