--@s记录某个车任意一段(中间不停车)的资料。假设
declare @s table(车号 varchar(10),从 varchar(10),到 varchar(10))
insert @s(车号,从,到)
select '920','重庆南路','西藏南路') union all
select '920','西藏南路','重庆南路') union all
select '920','西藏南路','小北门') union all
select '920','小北门','西藏南路') union all
select '920','小北门','老西门') union all
select '920','老西门','小北门')
--请在上面继续补充其它路车资料
--假设查找从@from到@to的路线
--1.不需要转车
select distinct 车号,@from as 上车,@to as 下车
from @t as a
inner join @t as b on b.车号=a.车号
where a.从=@from and b.到=@to
--2.需要转一次车
select distinct 车号,@from as 上车,@to as 下车
from @t as a
inner join @t as b on b.车号=a.车号
inner join @t as c on b.到=c.从
inner join @t as d on d.车号=c.车号
where a.从=@from and d.到=@to and b.车号<>c.车号
--2.需要转两次车
select distinct 车号,@from as 上车,@to as 下车
from @t as a
inner join @t as b on b.车号=a.车号
inner join @t as c on b.到=c.从
inner join @t as d on d.车号=c.车号
inner join @t as e on e.到=d.从
inner join @t as f on f.车号=e.车号
where a.从=@from and d.到=@to and b.车号<>c.车号 and e.车号<>d.车号
--综合以上结果
select distinct 车号,@from as 上车,@to as 下车
from @t as a
inner join @t as b on b.车号=a.车号
where a.从=@from and b.到=@to
union all
select distinct 车号,@from as 上车,@to as 下车
from @t as a
inner join @t as b on b.车号=a.车号
inner join @t as c on b.到=c.从
inner join @t as d on d.车号=c.车号
where a.从=@from and d.到=@to and b.车号<>c.车号
select distinct 车号,@from as 上车,@to as 下车
from @t as a
inner join @t as b on b.车号=a.车号
inner join @t as c on b.到=c.从
inner join @t as d on d.车号=c.车号
inner join @t as e on e.到=d.从
inner join @t as f on f.车号=e.车号
where a.从=@from and d.到=@to and b.车号<>c.车号 and e.车号<>d.车号
set nocount on
declare @ table(xl int,zh int,xh int)
insert @ select 8,1,1
union all select 8,2,2
union all select 8,3,3
union all select 6,4,1
union all select 6,3,2
union all select 6,5,3
union all select 7,8,1
union all select 7,5,2
union all select 7,9,3
union all select 7,10,4
union all select 7,11,5
--select xl 线路,zh 站号,xh 序号 from @
--我给出开始站1,结束站9,求需要中转的站!
===================================================================
--我只能先假设你的公交车是来回的。就是非单程的。
declare @start int,@end int
set @start=1
set @end=9
select cast(rtrim(xl)+','+rtrim(zh) as varchar(1000)) as t,xl,zh into #t
from @
where zh=@start
insert #t select A.t+'到'+rtrim(B.xl)+','+rtrim(B.zh),B.xl,B.xh
from #t a,@ B
where a.zh<>b.zh and a.xl=b.xl
while not exists (select 1
from #t
where zh=@end)
and exists (select 1
from #t a,@ B
where a.zh=b.zh
and a.xl<>b.xl
and a.t+'转'+rtrim(B.xl)+','+rtrim(B.zh) not in (select t from #t)
and B.xl not in (select xl from #t))
begin
insert #t select A.t+'转'+rtrim(B.xl)+','+rtrim(B.zh),B.xl,B.zh
from #t a,@ B
where a.zh=b.zh
and a.xl<>b.xl
and a.t+'到'+rtrim(B.xl)+','+rtrim(B.zh) not in (select t from #t)
and B.xl not in (select xl from #t)
insert #t select A.t+'到'+rtrim(B.xl)+','+rtrim(B.zh),B.xl,B.zh
from #t a,@ B
where a.zh<>b.zh
and a.xl=b.xl
and not exists
(select 1 from #t where xl=b.xl and zh=b.zh)
end
select top 1 t from #t where zh=@end order by len(t)
DECLARE Getline CURSOR local FOR --定义游标
SELECT [To],[Cost]
FROM flight
where [from]=@InputFrom
OPEN Getline
FETCH NEXT FROM flight
INTO @temTo,@temcost
WHILE @@FETCH_STATUS = 0
BEGIN
if @bestLine<>'' then
begin
@minCost=cast(right(@bestLine,len(@bestLine)-charindex('Cost:',@bestLine)-5)as numeric(20,2))
end
set @CurrCost=@CurrCost+@temcost
select @Str=@Str+'['+@InputFrom+'-'+@temTo+']'
if @temTo=@inputTo then
begin
if @minCost=0 or @CurrCost<@minCost then
begin
@bestLine=@Str+'Cost:'+cast(@CurrCost as varchar)
@minCost=@CurrCost
Return @bestLine
end
end
if @CurrCost<@minCost
select @bestLine=dbo.FN_GetSubClass (@temTo,@inputTo,@bestLine,@CurrCost,@str,@minCost) --递归,自己调用自己。
else
Return @bestLine