谁能帮我把下面的代码再改一下:以下代码是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)
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 %>
<%
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 %>
b.Station=@s2 --charindex( @s2, b.Station)=1
或
改为a.Station like @s1+'%'
-------------------
declare @T table (col sysname)
insert @T select '中国人'select * from @t where charindex('中',col)=1--有记录,有“中”开始的字段
select * from @t where charindex('中国',col)=1--有记录,有“中国”开始的字段select * from @t where charindex('国',col)=1--没记录
select * from @t where charindex('国人',col)=1--没记录
(所影响的行数为 1 行)col
--------------------------------------------------------------------------------------------------------------------------------
中国人(所影响的行数为 1 行)col
--------------------------------------------------------------------------------------------------------------------------------
中国人(所影响的行数为 1 行)col
-------------------------------------------------------------------------------------------------------------------------------- (所影响的行数为 0 行)col
-------------------------------------------------------------------------------------------------------------------------------- (所影响的行数为 0 行)
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 --这里改Station like @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(
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 charindex(@s1, a.Station)=1
and charindex(@s2,b.Station)=1
and a.id=b.id
)
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 3 --指定前三个方案
a.id,a.orders,b.orders,null,null,null from t_line a,t_line b
where charindex(@s1, a.Station)=1
and charindex(@s2,b.Station)=1
and a.id=b.id
order by abs(a.orders-b.orders)
else
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 3 --指定前三个方案
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) )
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=''
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+'-> ' 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
) and t.row= @row -------新增条件
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
insert @NewTable select @r+' '+@r1 as Result --新增到插入表fetch next from C_cursor into @row
delete @tr --初始化
end
close C_cursor
deallocate C_cursorselect Result from @NewTable ---返回最终结果集
go
alter 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 charindex(@s1, a.Station)=1
and charindex(@s2,b.Station)=1
and a.id=b.id
)
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 3 --指定前三个方案
a.id,a.orders,b.orders,null,null,null from t_line a,t_line b
where charindex(@s1, a.Station)=1
and charindex(@s2,b.Station)=1
and a.id=b.id
order by abs(a.orders-b.orders)
else
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 3 --指定前三个方案
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) )
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=''
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+'-> ' from @tr
if @r='' --这里改改
begin
close C_cursor
deallocate C_cursor
goto Result
end
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
) and t.row= @row -------新增条件
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
insert @NewTable select @r+' '+@r1 as Result --新增到插入表fetch next from C_cursor into @row
delete @tr --初始化
end
close C_cursor
deallocate C_cursorResult:
select Result from @NewTable ---返回最终结果集
go
8路:8路:255路:站D 20路:20路:20路:站J
8路:8路:255路:站D-> 站J 20路:20路:20路:站L-> 站J
出现以上效果的.
8路:8路:255路:站D 20路:20路:20路:站J
8路:8路:255路:站D-> 站J 20路:20路:20路:站L-> 站J
出现以上效果的.
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'站A',0 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'站J2',5 UNION ALL
SELECT N'20路' ,N'站L',6 UNION ALL
SELECT N'20路' ,N'站M',7 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
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 charindex(@s1, a.Station)=1
and charindex(@s2,b.Station)=1
and a.id=b.id
)
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 3 --指定前三个方案
a.id,a.orders,b.orders,null,null,null from t_line a,t_line b
where charindex(@s1, a.Station)=1
and charindex(@s2,b.Station)=1
and a.id=b.id
order by abs(a.orders-b.orders)
else
insert @t (id,o1,o2,id2,o3,o4) --指定列
select top 3 --指定前三个方案
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) )
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=''
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+'-> ' from @tr
if @r='' --这里改改
begin
close C_cursor
deallocate C_cursor
goto Result
end
else
select @r=id+':'+left(@r,len(@r)-2) from @t where row= @row
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
) ) and t.row= @row -------新增条件
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 where row= @row
insert @NewTable select @r+' '+@r1 as Result --新增到插入表fetch next from C_cursor into @row
delete @tr --初始化
select @r1='', @r='' --初始化
end
close C_cursor
deallocate C_cursorResult:
select Result from @NewTable ---返回最终结果集go
exec getline '站d','站I'
goResult
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
255路:站D-> 站J 20路:站J-> 站I
8路:站D-> 站J 20路:站J-> 站I
8路:站D-> 站J-> 站L 20路:站L-> 站J2-> 站J-> 站I
可选择在那个站下车...
8路:站D-> 站J-> 站L 20路:站L-> 站J2-> 站J-> 站I
8路:站D-> 站J 20路:站J-> 站I
--测多一条
exec getline '站A','站L'
Result
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8路:站A-> 站B-> 站C-> 站D-> 站J-> 站L
20路:站A-> 站G-> 站H-> 站I-> 站J-> 站J2-> 站L
上面要是少了一个"站",就不可以实现模糊搜索了.
1、模糊搜索只局限于起点和终点站,中间查询采用=连接
2、因为模糊搜索只局限于起点和终点站,可以用like '%XXX%'来模糊搜索
可用a.Station like '%'+@s1+'%'
或
patindex('%'+@s1+'%' ,a.Station)>0
charindex(@s1 ,a.Station)>0