--最短乘车路线查询示例(邹老大的。) 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) --*/
找了Yang_(扬帆破浪)效率 更快的 先谢谢两位先 ... 但是还是没有我想要的查询结果alter proc getline @s1 nvarchar(10), @s2 nvarchar(10) as set nocount on
declare @t table( id nvarchar(10), o1 int, o2 int, id2 nvarchar(10), o3 int, o4 int )
if exists ( select 1 from t_line a,t_line b where a.Station=@s1 and b.Station=@s2 and a.id=b.id ) insert @t select top 1 a.id,a.orders,b.orders,null,null,null from t_line a,t_line b where a.Station=@s1 and b.Station=@s2 and a.id=b.id order by abs(a.orders-b.orders) else insert @t select top 1 a.id,a.orders as o1,c.orders as o2,b.id as id2,d.orders as o3,b.orders as o4 from t_line a,t_line b,t_line c,t_line d where a.Station=@s1 and b.Station=@s2 and a.id=c.id and d.id=b.id and c.Station=d.Station order by abs(a.orders-c.orders)+abs(d.orders-b.orders)
set @r='' insert @tr select l.station from t_line l,@t t where l.id=t.id and (l.orders between t.o1 and t.o2 or l.orders between t.o2 and t.o1 ) order by case when t.o1 < t.o2 then l.orders else -l.orders end
select @r=@r+station+'->' from @tr
if @r='' return else select @r=id+':'+left(@r,len(@r)-2) from @t
delete @tr set @r1='' insert @tr select l.station from t_line l,@t t where l.id=t.id2 and (l.orders between t.o3 and t.o4 or l.orders between t.o4 and t.o3 ) order by case when t.o3 < t.o4 then l.orders else -l.orders end
select @r1=@r1+station+'->' from @tr
if @r1<>'' select @r1=id2+':'+left(@r1,len(@r1)-2) from @t
select @r+' '+@r1 as Result
go
----支持两次转车create FUNCTION dbo.splitString ( @string VARCHAR(8000), @delimiter CHAR(5), @rep int =1 ) RETURNS varchar(100) BEGIN DECLARE @start INT, @end INT SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) declare @ic int set @ic = 1 WHILE @start < LEN(@string) + 1 BEGIN if(@ic = @rep) return substring(@string, @start, @end-@start) IF @end = 0 SET @end = LEN(@string) + 1 SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) if (@end = 0) return '' set @ic = @ic + 1 END return null END go create function f_getcharcount( @str varchar(8000), @chr varchar(20) ) returns int as begin declare @re int,@i int select @re=0,@i=charindex(@chr,@str)+1 while @i>1 select @re=@re+1 ,@str=substring(@str,@i,8000) ,@i=charindex(@chr,@str)+1 return(@re) end go 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 goCREATE 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(Station) +' 坐'+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(200)), 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 起点站, 终点站, left(px2,charindex(':',px2) - 1)+'(坐'+ltrim(px1+1) + '站)到 '+ REVERSE(left (REVERSE(px2),charindex('>',REVERSE(px2))-1))+ case when px3 > 0 then '转乘' + left(px4,charindex(':',px4) - 1)+'(坐'+ltrim(px3+1) + '站)到 '+ REVERSE(left (REVERSE(px4),charindex('>',REVERSE(px4))-1)) else '' end + case when px5 > 0 then '转乘' + left(px6,charindex(':',px6) - 1)+'(坐'+ltrim(px5+1) + '站)到 '+ REVERSE(left (REVERSE(px6),charindex('>',REVERSE(px6))-1)) else ')' end as 乘车概要, 乘车线路 from( SELECT N'起点站'=@Station_Start , N'终点站'=@Station_Stop , dbo.f_getcharcount(dbo.splitString(Line+'转乘 坐','转乘 坐',1),'->') as px1, dbo.splitString(Line,'转乘 坐',1) as px2, dbo.f_getcharcount(dbo.splitString(Line+'转乘 坐','转乘 坐',2),'->') as px3, dbo.splitString(Line+'转乘 坐','转乘 坐',2) as px4, dbo.f_getcharcount(dbo.splitString(Line+'转乘 坐','转乘 坐',3),'->') as px5, dbo.splitString(Line+'转乘 坐','转乘 坐',3) as px6, N'乘车线路'=Line+N')' FROM # WHERE [Level]=@l AND Station=@Station_Stop ) a IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查 SELECT * FROM # GO
谢谢啊 都谢谢大家 我自己改了 扬帆那个了 效率 和 显示结果都变好了 加分了 哈哈 SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOALTER proc getline @s1 nvarchar(10), @s2 nvarchar(10) as set nocount on
declare @t table( row int identity, ---新增一列唯一值 id nvarchar(10), o1 int, o2 int, id2 nvarchar(10), o3 int, o4 int )
if exists ( select 1 from t_line a,t_line b where a.Station like '%'+@s1+'%' and b.Station like '%'+@s2+'%' and a.id=b.id )
insert @t (id,o1,o2,id2,o3,o4) --指定列 select top 15 --指定前三个方案 a.id,a.orders,b.orders,null,null,null from t_line a,t_line b where a.Station like '%'+@s1+'%' and b.Station like '%'+@s2+'%' and a.id=b.id order by abs(a.orders-b.orders) else insert @t (id,o1,o2,id2,o3,o4) --指定列 select top 15 --指定前三个方案 a.id, a.orders as o1, c.orders as o2, b.id as id2, d.orders as o3, b.orders as o4 from t_line a,t_line b,t_line c,t_line d where a.Station like '%'+@s1+'%' and b.Station like '%'+@s2+'%' and a.id=c.id and d.id=b.id and c.Station=d.Station order by abs(a.orders-c.orders)+abs(d.orders-b.orders) declare @count int declare @count1 int declare @r nvarchar(800) declare @r1 nvarchar(800) declare @zc nvarchar(20) declare @tr table ( station Nvarchar(10) )
declare @NewTable table (Result nvarchar(1000))--记录结果declare C_cursor cursor for select row from @T t declare @row int open C_cursor fetch next from C_cursor into @row while @@fetch_status=0 begin
set @r='' set @count=0 insert @tr select l.station from t_line l,@t t where ( l.id=t.id and (l.orders between t.o1 and t.o2 or l.orders between t.o2 and t.o1 ) ) and t.row= @row -------新增条件 order by case when t.o1 < t.o2 then l.orders else -l.orders end
select @r=@r+station+'-> ',@count=@count+1 from @tr
if @r='' --这里改改 begin close C_cursor deallocate C_cursor goto Result end else select @r=id+':'+left(@r,len(@r)-2)+'(坐'+ cast( @count-1 as varchar) +'站)' from @t where row= @row
delete @tr set @r1='' set @count1=0 set @zc=' ' insert @tr select l.station from t_line l,@t t where ( l.id=t.id2 and (l.orders between t.o3 and t.o4 or l.orders between t.o4 and t.o3 ) ) and t.row= @row -------新增条件 order by case when t.o3 < t.o4 then l.orders else -l.orders end
select @r1=@r1+station+'-> ',@count1=@count1+1 from @tr
if @r1 <> ''
select @zc=' 转乘 ' , @r1=id2+':'+left(@r1,len(@r1)-2)+'(坐'+ cast( @count1-1 as varchar) +'站)' from @t where row= @row
insert @NewTable select @r+@zc+@r1 as Result --新增到插入表fetch next from C_cursor into @row delete @tr --初始化 select @r1='', @r='',@count=0,@count1=0 --初始化 end close C_cursor deallocate C_cursorResult: select Result from @NewTable ---返回最终结果集 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOexec getline N'站A',N'站Q' Result ------------------------------------------------------------------------------------------------------------------------------8路:站A-> 站B-> 站C-> 站D-> 站J(坐4站) 转乘 255路:站J-> 站Q(坐1站) 8路:站A-> 站B-> 站C-> 站D(坐3站) 转乘 255路:站D-> 站J-> 站Q(坐2站)
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)
--*/
先谢谢两位先 ...
但是还是没有我想要的查询结果alter proc getline
@s1 nvarchar(10),
@s2 nvarchar(10)
as
set nocount on
declare @t table(
id nvarchar(10),
o1 int,
o2 int,
id2 nvarchar(10),
o3 int,
o4 int
)
if exists (
select 1 from t_line a,t_line b
where a.Station=@s1
and b.Station=@s2
and a.id=b.id
)
insert @t
select top 1 a.id,a.orders,b.orders,null,null,null from t_line a,t_line b
where a.Station=@s1
and b.Station=@s2
and a.id=b.id
order by abs(a.orders-b.orders)
else
insert @t
select top 1 a.id,a.orders as o1,c.orders as o2,b.id as id2,d.orders as o3,b.orders as o4 from t_line a,t_line b,t_line c,t_line d
where a.Station=@s1
and b.Station=@s2
and a.id=c.id
and d.id=b.id
and c.Station=d.Station
order by abs(a.orders-c.orders)+abs(d.orders-b.orders)
declare @r nvarchar(800)
declare @r1 nvarchar(800)
declare @tr table (
station Nvarchar(10)
)
set @r=''
insert @tr
select l.station
from t_line l,@t t
where l.id=t.id
and (l.orders between t.o1 and t.o2
or l.orders between t.o2 and t.o1
)
order by case when t.o1 < t.o2 then l.orders else -l.orders end
select @r=@r+station+'->' from @tr
if @r=''
return
else
select @r=id+':'+left(@r,len(@r)-2) from @t
delete @tr
set @r1=''
insert @tr
select l.station
from t_line l,@t t
where l.id=t.id2
and (l.orders between t.o3 and t.o4
or l.orders between t.o4 and t.o3
)
order by case when t.o3 < t.o4 then l.orders else -l.orders end
select @r1=@r1+station+'->' from @tr
if @r1<>''
select @r1=id2+':'+left(@r1,len(@r1)-2) from @t
select @r+' '+@r1 as Result
go
(
@string VARCHAR(8000),
@delimiter CHAR(5),
@rep int =1
)
RETURNS varchar(100)
BEGIN DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
declare @ic int
set @ic = 1
WHILE @start < LEN(@string) + 1
BEGIN
if(@ic = @rep) return substring(@string, @start, @end-@start)
IF @end = 0
SET @end = LEN(@string) + 1 SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
if (@end = 0) return ''
set @ic = @ic + 1
END return null
END
go
create function f_getcharcount(
@str varchar(8000),
@chr varchar(20)
) returns int
as
begin
declare @re int,@i int
select @re=0,@i=charindex(@chr,@str)+1
while @i>1
select @re=@re+1
,@str=substring(@str,@i,8000)
,@i=charindex(@chr,@str)+1
return(@re)
end
go
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
goCREATE 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(Station) +' 坐'+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(200)),
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 起点站,
终点站,
left(px2,charindex(':',px2) - 1)+'(坐'+ltrim(px1+1) + '站)到 '+
REVERSE(left (REVERSE(px2),charindex('>',REVERSE(px2))-1))+
case when px3 > 0 then '转乘' + left(px4,charindex(':',px4) - 1)+'(坐'+ltrim(px3+1) + '站)到 '+
REVERSE(left (REVERSE(px4),charindex('>',REVERSE(px4))-1)) else '' end +
case when px5 > 0 then '转乘' + left(px6,charindex(':',px6) - 1)+'(坐'+ltrim(px5+1) + '站)到 '+
REVERSE(left (REVERSE(px6),charindex('>',REVERSE(px6))-1)) else ')' end as 乘车概要,
乘车线路
from(
SELECT N'起点站'=@Station_Start ,
N'终点站'=@Station_Stop ,
dbo.f_getcharcount(dbo.splitString(Line+'转乘 坐','转乘 坐',1),'->') as px1,
dbo.splitString(Line,'转乘 坐',1) as px2,
dbo.f_getcharcount(dbo.splitString(Line+'转乘 坐','转乘 坐',2),'->') as px3,
dbo.splitString(Line+'转乘 坐','转乘 坐',2) as px4,
dbo.f_getcharcount(dbo.splitString(Line+'转乘 坐','转乘 坐',3),'->') as px5,
dbo.splitString(Line+'转乘 坐','转乘 坐',3) as px6,
N'乘车线路'=Line+N')'
FROM #
WHERE [Level]=@l
AND Station=@Station_Stop ) a
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
SELECT * FROM #
GO
--调用
EXEC p_qry N'站A',N'站Q' drop table T_Line
drop proc p_qrydrop function splitString,f_getcharcount
/*起点站 终点站 乘车概要 乘车线路
---------- ---------- ---------------------------------------------------------------- -----------------------------------------------------------------------------
站A 站Q (从 站A 坐8路(坐5站)到 站J 下车 转乘乘 坐 255路(坐2站)到 站Q) (从 站A 坐8路: 站A->站B->站C->站D->站J 下车 转乘 坐 255路: 站J->站Q)
站A 站Q (从 站A 坐8路(坐4站)到 站D 下车 转乘乘 坐 255路(坐3站)到 站Q) (从 站A 坐8路: 站A->站B->站C->站D 下车 转乘 坐 255路: 站D->站J->站Q)
*/
加分了
哈哈 SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER proc getline
@s1 nvarchar(10),
@s2 nvarchar(10)
as
set nocount on
declare @t table(
row int identity, ---新增一列唯一值
id nvarchar(10),
o1 int,
o2 int,
id2 nvarchar(10),
o3 int,
o4 int
)
if exists (
select 1 from t_line a,t_line b
where a.Station like '%'+@s1+'%'
and b.Station like '%'+@s2+'%'
and a.id=b.id
)
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 15 --指定前三个方案
a.id,a.orders,b.orders,null,null,null from t_line a,t_line b
where a.Station like '%'+@s1+'%'
and b.Station like '%'+@s2+'%'
and a.id=b.id
order by abs(a.orders-b.orders)
else
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 15 --指定前三个方案
a.id,
a.orders as o1,
c.orders as o2,
b.id as id2,
d.orders as o3,
b.orders as o4
from
t_line a,t_line b,t_line c,t_line d where a.Station like '%'+@s1+'%'
and b.Station like '%'+@s2+'%'
and a.id=c.id
and d.id=b.id
and c.Station=d.Station
order by abs(a.orders-c.orders)+abs(d.orders-b.orders)
declare @count int
declare @count1 int
declare @r nvarchar(800)
declare @r1 nvarchar(800)
declare @zc nvarchar(20)
declare @tr table ( station Nvarchar(10) )
declare @NewTable table (Result nvarchar(1000))--记录结果declare C_cursor cursor for
select row from @T t
declare @row int
open C_cursor
fetch next from C_cursor into @row
while @@fetch_status=0
begin
set @r=''
set @count=0
insert @tr
select l.station
from t_line l,@t t
where ( l.id=t.id
and (l.orders between t.o1 and t.o2
or l.orders between t.o2 and t.o1
) ) and t.row= @row -------新增条件
order by
case when t.o1 < t.o2 then l.orders else -l.orders end
select @r=@r+station+'-> ',@count=@count+1 from @tr
if @r='' --这里改改
begin
close C_cursor
deallocate C_cursor
goto Result
end
else
select @r=id+':'+left(@r,len(@r)-2)+'(坐'+ cast( @count-1 as varchar) +'站)' from @t where row= @row
delete @tr
set @r1=''
set @count1=0
set @zc=' '
insert @tr
select l.station
from t_line l,@t t
where ( l.id=t.id2
and (l.orders between t.o3 and t.o4
or l.orders between t.o4 and t.o3
) ) and t.row= @row -------新增条件
order by case when t.o3 < t.o4 then l.orders else -l.orders end
select @r1=@r1+station+'-> ',@count1=@count1+1 from @tr
if @r1 <> ''
select @zc=' 转乘 ' , @r1=id2+':'+left(@r1,len(@r1)-2)+'(坐'+ cast( @count1-1 as varchar) +'站)' from @t where row= @row
insert @NewTable select @r+@zc+@r1 as Result --新增到插入表fetch next from C_cursor into @row
delete @tr --初始化
select @r1='', @r='',@count=0,@count1=0 --初始化
end
close C_cursor
deallocate C_cursorResult:
select Result from @NewTable ---返回最终结果集
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOexec getline N'站A',N'站Q' Result
------------------------------------------------------------------------------------------------------------------------------8路:站A-> 站B-> 站C-> 站D-> 站J(坐4站) 转乘 255路:站J-> 站Q(坐1站)
8路:站A-> 站B-> 站C-> 站D(坐3站) 转乘 255路:站D-> 站J-> 站Q(坐2站)
你一开始 贴那个,我就只改了那个呀