问站Ａ－站Ｌ最短乘车路线(SQL问题)

--问站Ａ－站Ｌ最短乘车路线(SQL问题)
CREATE TABLE T_Line(
ID      nvarchar(10),  --公交线路号
Station nvarchar(10),  --站点名称
Orders  int)           --行车方向(通过它反应每个站的上一个、下一个站)
INSERT T_Line
SELECT N'8路'  ,N'站A',1 UNION ALL
SELECT N'8路'  ,N'站B',2 UNION ALL
SELECT N'8路'  ,N'站C',3 UNION ALL
SELECT N'8路'  ,N'站D',4 UNION ALL
SELECT N'8路'  ,N'站J',5 UNION ALL
SELECT N'8路'  ,N'站L',6 UNION ALL
SELECT N'8路'  ,N'站M',7 UNION ALL
SELECT N'20路' ,N'站G',1 UNION ALL
SELECT N'20路' ,N'站H',2 UNION ALL
SELECT N'20路' ,N'站I',3 UNION ALL
SELECT N'20路' ,N'站J',4 UNION ALL
SELECT N'20路' ,N'站L',5 UNION ALL
SELECT N'20路' ,N'站M',6 UNION ALL
SELECT N'255路',N'站N',1 UNION ALL
SELECT N'255路',N'站O',2 UNION ALL
SELECT N'255路',N'站P',3 UNION ALL
SELECT N'255路',N'站Q',4 UNION ALL
SELECT N'255路',N'站J',5 UNION ALL
SELECT N'255路',N'站D',6 UNION ALL
SELECT N'255路',N'站E',7 UNION ALL
SELECT N'255路',N'站F',8
GO
select * from T_Line

--问Ａ　　－　　Ｌ最短乘车路线

/*--例如
起点站   终点站      乘车线路
---------- ------------ -----------------------------------------------------------
站A         站L         (8路: 站A->站B->站C->站D->站J->站L)
--*/

 

create proc p_wwww
@st varchar(10),
@en varchar(10)
as
if(@st<>@en)
begin
select *,cast(station as varchar(500))+'乘'+id line,
1 cunt,cast(station as varchar(500))[order],1[degree] into #
from t_line where station = @st

declare @cunt int
set @cunt=0
while(@@rowcount>0)
begin
set @cunt=@cunt+1
insert into #
select a.*,
line+case when a.id<>b.id then '-'+a.station+'转'+a.id else '-'+a.station end,
@cunt+1,[order]+'-'+a.station,case when a.id<>b.id then b.degree+1 else b.degree end
from t_line a
join
(select a.station[newstation],b.id,b.station[oldstation],b.line,[order],b.degree from
(select * from t_line)a
join
(select * from # where cunt=@cunt and station<>@en)b
on b.id=a.id and abs(b.orders-a.orders)=1)b
on b.newstation = a.station
where charindex(a.station,[order])=0
end

select @st[起点站],@en[终点站],a.line[乘车线路] from # a
join
(select [order],min(degree)degree from # where station = @en group by [order])b
on b.[order]=a.[order] and b.degree=a.degree
end
else
select @st[起点站],@en[终点站],'无需乘车'[乘车线路]

 

--------------------------------------------------测试
exec p_wwww '站F','站A'
exec p_wwww '站A','站A'
exec p_wwww '站D','站J'
exec p_wwww '站A','站L'
exec p_wwww '站A','站Q'