问站Ａ－站Ｌ最短乘车路线(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 Procedure test
@start varchar(20),
@stop varchar(20)
AS
set nocount on
declare @l int
set @l=0
select ID,Station,Line=case( '( '+Rtrim(ID)+ ': '+Rtrim(station) as nvarchar(4000)),
orders=orders,
level=@l
into #t from T_line
where station=@start
while @@rowcount> 0 and not exists(select 1 from #t where station=@stop)
begin
set @l=@l+1
insert into #t(line,id,station,orders,level)
select line=a.line+case when a.id=b.id then N '-> '+Rtrim(b.station)
else N ') ( '+Rtrim(b.id)+N ': '+Rtrim(b.station) end,
b.id,b.station,b.orders,@l
from #t a,T_line b
where a.level=@l-1
and (a.station=b.station and a.id <> b.id or a.id=b.id and (a.orders=b.orders+1 or a.orders=b.orders-1))
and len(a.line) <4000
and patindex( '%[> ] '+b.station+ '[-)]% ',a.line)=0
end
select N 'begin '=@start,N 'end '=@stop,N 'line '=line+ ') '
from #t
where level=@l
and station=@stop
if @@rowcount=0
select * from #t
GO
[解决办法]
create proc p_qry
@station_start nvarchar(10),
@station_stop nvarchar(10)
as
set nocount on
declare @l int
set @l = 0
select id,station,line = case( '( '+ rtrim(id) + ': ' + rtrim(station) as nvarchar(4000)),orders = orders,[level] = @l into # from T_line where station = @station_start
while @@rowcount > 0 and not exists(select * from # where station = @station_stop)
begin
set @l = @l + 1
insert #(line,id,station,orders,[level])
select line = a.line+case when a.id = b.id then n ' -> '+ rtrim(b.station) else n ') ** ( ' + rtrim(b.id) + n ': ' + rtrim(b.station) end,b.id,b.station,b.orders,@l from # a,t_line b where a.[level] = @l - 1 and (a.station = b.station and a.id <> b.id or a.id = b.id and (a.orders = b.orders + 1 or a.orders = b.orders - 1)) and len(a.line) < 4000 and patindex( '%[> ] ' + b.station + '[-)]% ',a.line) = 0 end
select n '起点站 ' = @station_start,n '终点站 ' = @station_stop,n '乘车线路 ' = line + n ') '
from # where [level] = @l and station = @station_stop
if @@rowcount = 0
select * from #
go
--调用
exec p_qry n '站A ',n '站L '
