给个类似,参考一下吧`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)
--*/
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)
--*/
create table checi
(
Code varchar(20),
FC nvarchar(20),
TC nvarchar(20),
LT varchar(20),
AT varchar(20),
Dis int
)
goinsert into checi
select 'T2','广州','北京','5:00','20:00',1800
union all select 'T3','北京','广州','5:00','20:00',1800
union all select 'Y2','兰州','厦门','5:00','20:00',1200
union all select 'Y3','厦门','兰州','5:00','20:00',1200
gocreate table chezhan
(
Id int identity(1,1),
Code varchar(20),
CN nvarchar(20),
SN int,
AT varchar(20),
LT varchar(20),
Dis int
)
goselect * from chezhan
insert into chezhan
select 'T2','广州',1,'5:00',null,0
union all select 'T2','株洲',2,'8:05','8:00',360
union all select 'T2','武汉',3,'11:05','11:00',360
union all select 'T2','郑州',4,'14:05','14:00',360
union all select 'T2','石家庄',5,'17:05','17:00',360
union all select 'T2','北京',6,null,'20:00',360
union all select 'T3','北京',1,'5:00',null,0
union all select 'T3','石家庄',2,'8:05','8:00',360
union all select 'T3','郑州',3,'11:05','11:00',360
union all select 'T3','武汉',4,'14:05','14:00',360
union all select 'T3','株洲',5,'17:05','17:00',360
union all select 'T3','广州',6,null,'20:00',360
union all select 'Y2','兰州',1,'5:00',null,0
union all select 'Y2','西安',2,'8:05','8:00',240
union all select 'Y2','襄樊',3,'11:05','11:00',240
union all select 'Y2','武汉',4,'14:05','14:00',240
union all select 'Y2','南昌',5,'17:05','17:00',240
union all select 'Y2','厦门',6,null,'20:00',240
union all select 'Y3','厦门',1,'5:00',null,0
union all select 'Y3','南昌',2,'8:05','8:00',240
union all select 'Y3','武汉',3,'11:05','11:00',240
union all select 'Y3','襄樊',4,'14:05','14:00',240
union all select 'Y3','西安',5,'17:05','17:00',240
union all select 'Y3','兰州',6,null,'20:00',240 create proc up_GetCheCiInfo
(
@Begin nvarchar(20),
@End nvarchar(20)
)
as
begin select c.code as 车次,c.FC as 始发站,c.TC as 终点站,
temp.CN 站名 ,temp.AT as 到站时间,Temp.LT as 离站时间,temp.Dis 中途距离,SN
from checi c
inner join
(
select z.* from chezhan z
inner join
(
select b.Code,b.SN as beginSn,e.SN as EndSN
from chezhan b
inner join chezhan e
on b.Code = e.Code and b.SN < e.SN and e.CN = @End
where b.CN = @Begin
) temp
on z.code = temp.code and z.SN >= beginSN and z.SN <= EndSN
) temp
on temp.Code = c.Code
order by 车次,SN
endexec up_GetCheCiInfo '武汉','西安'结果为:车次 始发站 终点站 站名 到站时间 离站时间 中途距离 SN
Y3 厦门 兰州 武汉 11:05 11:00 240 3
Y3 厦门 兰州 襄樊 14:05 14:00 240 4
Y3 厦门 兰州 西安 17:05 17:00 240 5
@fc nvarchar(20),
@tc nvarchar(20)
AS
begin
declare @Code nvarchar(20)
set @Code=''
select @Code=b.Code from b inner join b a on a.code=b.code
where b.cn=@fc and a.cn=@tcif @Code<>''
begin select *
from b tem
where ID >=( select ID from b where cn=@fc and Code=tem.Code) and ID <=( select ID from b where Code=tem.Code and cn=@tc)
order by Code
end
else
begin
declare @S nvarchar(20)
declare @E nvarchar(20)
select t.ID as SID, T.CODE AS SCode,t.CN ,y.ID AS EID,Y.CODE AS ECode
into #aa
from ( select *
from b tem
where ID >=( select ID from b where cn=@fc and Code=tem.Code) ) t
inner join
( select *
from b tem
where ID <=( select ID from b where Code=tem.Code and cn=@tc))y
on t.cn=y.cn select tem.*
from b tem inner join #AA on tem.code=#aa.scode
where ID >=( select ID from b where cn=@fc and Code=tem.Code) and ID<=#aa.sid
union
select tem.*
from b tem inner join #AA on tem.code=#aa.ecode
where ID <=( select ID from b where Code=tem.Code and cn=@tc) and ID>=#aa.eid
end
end
GO--
aes_Code N'株洲',N'西安'
--结果
2 T2 株洲 2 8:05 8:00 360
3 T2 武汉 3 11:05 11:00 360
21 Y3 武汉 3 11:05 11:00 240
22 Y3 襄樊 4 14:05 14:00 240
23 Y3 西安 5 17:05 17:00 240aes_Code N'武汉',N'西安'
--j结果
21 Y3 武汉 3 11:05 11:00 240
22 Y3 襄樊 4 14:05 14:00 240
23 Y3 西安 5 17:05 17:00 240
大哥你这个只能实现一次转车!如果我要转两次,或以上呢?