两个表
表1
id，车号，顺序,站名
1,"1",1,"a"
2,"1",2,"b"
3,"1",3,"c"
4,"1",4,"d"
5,"1",5,"e"
6,"1",6,"f"
7,"1",7,"g"
8,"1",8,"h"
9,"2",1,"f"
10,"2",2,"i"
11,"2",3,"j"
12,"2",4,"k"
13,"2",5,"l"
14,"2",6,"m"表2
id，站名，车号
1,"a","1"
2,"b","1"
3,"c","1"
4,"d","1"
5,"e","1"
6,"f","1"
7,"f","2"
8,"g","1"
9,"h","1"
10,"i","2"
11,"j","2"
12,"k","2"
13,"l","2"
14,"m","2"我要查询 从 a站 到 b站
应该怎么做车？要求能回答出 做哪几路车？在什么站转车？ 最短路径（这个不需要加权运算）说明
表1中的顺序是指 站台的顺序有兴趣的朋友讨论讨论
:)
表1
id，车号，顺序,站名
1,"1",1,"a"
2,"1",2,"b"
3,"1",3,"c"
4,"1",4,"d"
5,"1",5,"e"
6,"1",6,"f"
7,"1",7,"g"
8,"1",8,"h"
9,"2",1,"f"
10,"2",2,"i"
11,"2",3,"j"
12,"2",4,"k"
13,"2",5,"l"
14,"2",6,"m"表2
id，站名，车号
1,"a","1"
2,"b","1"
3,"c","1"
4,"d","1"
5,"e","1"
6,"f","1"
7,"f","2"
8,"g","1"
9,"h","1"
10,"i","2"
11,"j","2"
12,"k","2"
13,"l","2"
14,"m","2"我要查询 从 a站 到 b站
应该怎么做车？要求能回答出 做哪几路车？在什么站转车？ 最短路径（这个不需要加权运算）说明
表1中的顺序是指 站台的顺序有兴趣的朋友讨论讨论
:)
如：
create table A
(
起始地 varchar(5),
目的地 varchar(5),
距离 int
)insert A select 'A','B',1000
insert A select 'A','C',1100
insert A select 'A','D',900
insert A select 'A','E',400
insert A select 'B','D',300
insert A select 'D','F',600
insert A select 'E','A',400
insert A select 'F','G',1000
insert A select 'C','B',600declare @Q varchar(5)
declare @T table ( 起始地 varchar(5),目的地 varchar(5),路 varchar(8000),lev int)
declare @lev int
set @Q='A'
set @lev=1
insert @T select 起始地,目的地,起始地+'->'+目的地 as 路,@lev as lev from A where 起始地=@Q
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select A.起始地,A.目的地,T.路+'->'+A.目的地 ,@lev as lev from (select * from @T where lev=@lev-1) T,(select * from A where A.目的地 not in (select 起始地 from @T)) A where A.起始地=T.目的地
endselect 路 from @T
declare @t1 table(id int,车号 varchar(1),顺序 int,站名 varchar(2))
insert @t1
select 1,'1',1,'a' union all
select 2,'1',2,'b' union all
select 3,'1',3,'c' union all
select 4,'1',4,'d' union all
select 5,'1',5,'e' union all
select 6,'1',6,'f' union all
select 7,'1',7,'g' union all
select 8,'1',8,'m' union all
select 9,'2',1,'e' union all
select 10,'2',2,'i' union all
select 11,'2',3,'j' union all
select 12,'2',4,'k' union all
select 13,'2',5,'l' union all
select 14,'2',6,'m' union all
select 15,'3',1,'b' union all
select 16,'3',2,'e' union all
select 17,'3',3,'k' union all
select 18,'3',4,'m'select 车号,st= 站名 ,
ed=(select 站名 from @t1 where 车号 = a.车号 and 顺序= a.顺序+1)
into #
from @t1 a
where exists (select 1 from @t1 where 车号 = a.车号 and 顺序> a.顺序)declare @col char(2)
set @col = 'a'
declare @t table(col varchar(50),ch1 varchar(50),ch char(1),st varchar(20),ed varchar(20),level int)declare @i int
set @i=1
insert @t select st+'-'+ed,车号,*,@i from # where st=@col
while exists (select * from # a,@t b where
b.ed=a.st and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.ed,b.ch1+'-'+a.车号,a.车号,a.st,a.ed,@i from # a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed<>@col
end
select col as 站台的顺序,ch1 as 站转车
from @t
where left(col,1) = 'a' and right(col,1) = 'm' order by len(ch1)/*站台的顺序 站转车
-------------------------------------------------- -----------------
a-b-e-k-m 1-3-3-3
a-b-e-f-g-m 1-3-1-1-1
a-b-e-k-l-m 1-3-3-2-2
a-b-c-d-e-k-m 1-1-1-1-3-3
a-b-e-i-j-k-m 1-3-2-2-2-3
a-b-c-d-e-f-g-m 1-1-1-1-1-1-1
a-b-c-d-e-k-l-m 1-1-1-1-3-2-2
a-b-e-i-j-k-l-m 1-3-2-2-2-2-2
a-b-c-d-e-i-j-k-m 1-1-1-1-2-2-2-3
a-b-c-d-e-i-j-k-l-m 1-1-1-1-2-2-2-2-2
*/
--- top 1 最短路径
drop table #
declare @t1 table(id int,车号 varchar(1),顺序 int,站名 varchar(2))
insert @t1
select 1,'1',1,'a' union all
select 2,'1',2,'b' union all
select 3,'1',3,'c' union all
select 4,'1',4,'d' union all
select 5,'1',5,'e' union all
select 6,'1',6,'f' union all
select 7,'1',7,'g' union all
select 8,'1',8,'m' union all
select 9,'2',1,'e' union all
select 10,'2',2,'i' union all
select 11,'2',3,'j' union all
select 12,'2',4,'k' union all
select 13,'2',5,'l' union all
select 14,'2',6,'m' union all
select 15,'3',1,'b' union all
select 16,'3',2,'e' union all
select 17,'3',3,'k' union all
select 18,'3',4,'m'select 车号,st= 站名 ,
ed=(select 站名 from @t1 where 车号 = a.车号 and 顺序= a.顺序+1)
into #
from @t1 a
where exists (select 1 from @t1 where 车号 = a.车号 and 顺序> a.顺序)declare @col char(2)
set @col = 'a' ---设置起点站
declare @t table(col varchar(50),ch1 varchar(50),ch char(1),st varchar(20),ed varchar(20),level int)declare @i int
set @i=1
insert @t select st+'-'+ed,车号,*,@i from # where st=@col
while exists (select * from # a,@t b where
b.ed=a.st and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.ed,b.ch1+'-'+a.车号,a.车号,a.st,a.ed,@i from # a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed<>@col
end
select col as 站台的顺序,ch1 as 站转车
from @t
where left(col,1) = 'a' and right(col,1) = 'm' order by len(ch1)/*站台的顺序 站转车
-------------------------------------------------- -----------------
a-b-e-k-m 1-3-3-3
a-b-e-f-g-m 1-3-1-1-1
a-b-e-k-l-m 1-3-3-2-2
a-b-c-d-e-k-m 1-1-1-1-3-3
a-b-e-i-j-k-m 1-3-2-2-2-3
a-b-c-d-e-f-g-m 1-1-1-1-1-1-1
a-b-c-d-e-k-l-m 1-1-1-1-3-2-2
a-b-e-i-j-k-l-m 1-3-2-2-2-2-2
a-b-c-d-e-i-j-k-m 1-1-1-1-2-2-2-3
a-b-c-d-e-i-j-k-l-m 1-1-1-1-2-2-2-2-2
*/
--- top 1 最短路径
select top 1 col as 站台的顺序,ch1 as 站转车
from @t
where left(col,1) = 'a' and right(col,1) = 'm' order by len(ch1)/*
站台的顺序 站转车
-------------------------------------------------- ---------------
a-b-e-k-m 1-3-3-3*/
drop table #