34,593
社区成员
发帖
与我相关
我的任务
分享
Train 表
Name(车次) StartStation ENDStation
1522 哈尔滨 天津
...
TrainStation表
Train_Name (对应Train 表Name) StationId StationName City
1522 1 吉林 吉林
....
需求:如从哈尔滨-重庆的中转站,希望列出所有的中转站名称.
--测试数据
create table test(Train_Name varchar(10),StationId int,StationName nvarchar(10),City nvarchar(10))
insert test select '1526','1',N'哈尔滨',N'哈尔滨'
union all select '1526','2',N'石家庄',N'石家庄'
union all select 'T9','1',N'石家庄',N'石家庄'
union all select 'T9','2',N'长沙',N'长沙'
union all select 'T9','3',N'重庆',N'重庆'
union all select 'T10','1',N'重庆',N'重庆'
union all select 'T10','2',N'成都',N'成都'
union all select 'T12','1',N'广州',N'广州'
union all select 'T12','2',N'南昌',N'南昌'
union all select 'T11','1',N'北京',N'北京'
union all select 'T11','2',N'广州',N'广州'
union all select 'T13','1',N'哈尔滨',N'哈尔滨'
union all select 'T13','2',N'上海',N'上海'
go
--存储过程
create proc p (@start nvarchar(10),@end nvarchar(10))
as
--判断是否需转车
if exists(select 1 from test t
where Train_Name in (select Train_Name from test where StationName=@start)
and StationName=@end)
begin
select distinct 坐车方案=Train_Name+':'+@start+'——'+@end from test t
where Train_Name in (select Train_Name from test where StationName=@start)
and StationName=@end
return
end
declare @i int
set @i=1
--取起始车次
select *,0 i into #t from test t
where exists(select 1 from test where Train_Name=t.Train_Name and StationName=@start)
--计算最少中转次数
while not exists(select 1 from #t where StationName=@end) and @i<10
begin
insert #t
select *,@i from test t
where exists(select 1 from test a
inner join #t b on a.StationName=b.StationName
where a.Train_Name=t.Train_Name )
set @i=@i+1
end
if @i>=10
begin
select 坐车方案=N'中转次数过多或无车到达'
return
end
--组合坐车方案
select * into #t1 from #t t
where not exists(select 1 from #t where Train_Name=t.Train_Name and i<t.i)
select k=Train_Name+':'+@start+'——'+StationName+N' 转 '+
(select Train_Name from #t1 where StationName=t.StationName and i=1) ,
t=(select Train_Name from #t1 where StationName=t.StationName and i=1)
into #t2
from #t1 t
where i=0
and exists(select Train_Name from #t1 where StationName=t.StationName and i=1)
set @i=1
while not exists(select 1 from test a
inner join #t2 b on a.Train_Name=b.t
where a.StationName=@end )
begin
update a set k=k+':'+b.StationName+N' 转 '+
(select Train_Name from #t1 where StationName=b.StationName and i=@i+1),
t=(select Train_Name from #t1 where StationName=b.StationName and i=@i+1)
from #t2 a
inner join #t1 b on a.t=b.Train_Name
where exists(select 1 from #t1 where StationName=b.StationName and i=@i+1)
set @i=@i+1
end
select 坐车方案=k+':'+@end from #t2
go
exec p N'哈尔滨',N'石家庄'--直达
/*
---------------------------------
1526:哈尔滨——石家庄
*/
exec p N'上海',N'成都'--中转
/*
---------------------------------------------------------
T13:上海——哈尔滨 转 1526:石家庄 转 T9:重庆 转 T10:成都
*/
exec p N'北京',N'火星'--无法到达
/*
-----------
中转次数过多或无车到达
*/
AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop)
BEGIN
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
--乘车线路查询存储过程
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=CAST('('+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'
/*--结果
起点站 终点站 乘车线路
---------- ------------ -----------------------------------------------------------
站A 站L (8路: 站A->站B->站C->站D->站J->站L)
--*/
/*