--查询的函数
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns @re table(train varchar(20),stations varchar(8000))
as
begin
declare @s varchar(8000),@train varchar(20),@id1 int,@id2 int
declare tb cursor local for
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
open tb
fetch next from tb into @train,@id1,@id2
while @@fetch_status=0
begin
set @s=''
select @s=@s+','+station from 表 where id between @id1 and @id2
insert @re select @train,substring(@s,2,8000)
fetch next from tb into @train,@id1,@id2
end
close tb
deallocate tb
return
end
go--调用进行查询
select * from f_qry('重庆','广安')
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns @re table(train varchar(20),stations varchar(8000))
as
begin
declare @s varchar(8000),@train varchar(20),@id1 int,@id2 int
declare tb cursor local for
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
open tb
fetch next from tb into @train,@id1,@id2
while @@fetch_status=0
begin
set @s=''
select @s=@s+','+station from 表 where id between @id1 and @id2
insert @re select @train,substring(@s,2,8000)
fetch next from tb into @train,@id1,@id2
end
close tb
deallocate tb
return
end
go--调用进行查询
select * from f_qry('重庆','广安')
--测试--测试数据
create table 表(id int identity(1,1),train char(4),station varchar(10))
insert 表 select '1003','重庆'
union all select '1003','北碚'
union all select '1003','广安'
union all select '1004','庆北'
union all select '1004','北碚'
union all select '1004','广安'
union all select '1005','广安'
union all select '1005','北碚'
union all select '1005','重庆'
union all select '1005','广安'
go--查询的函数
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns @re table(train varchar(20),stations varchar(8000))
as
begin
declare @s varchar(8000),@train varchar(20),@id1 int,@id2 int
declare tb cursor local for
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
open tb
fetch next from tb into @train,@id1,@id2
while @@fetch_status=0
begin
set @s=''
select @s=@s+','+station from 表 where id between @id1 and @id2
insert @re select @train,substring(@s,2,8000)
fetch next from tb into @train,@id1,@id2
end
close tb
deallocate tb
return
end
go--调用进行查询
select * from f_qry('重庆','广安')
go--删除测试
drop table 表
drop function f_qry/*--测试结果
train stations
-------------------- ------------------
1003 重庆,北碚,广安
1005 重庆,广安(所影响的行数为 2 行)--*/
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns @re table(train varchar(20),stations varchar(8000))
as
begin
declare @s varchar(8000),@train varchar(20),@id1 int,@id2 int
declare tb cursor local for
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
open tb
fetch next from tb into @train,@id1,@id2
while @@fetch_status=0
begin
set @s=''
select @s=@s+','+station from 表 where id between @id1 and @id2
insert @re select @train,substring(@s,2,8000)
fetch next from tb into @train,@id1,@id2
end
close tb
deallocate tb
return
end
go--调用进行查询
select * from f_qry('重庆','广安')
( select a.* from test6 a where a.station in
(select station from test6 b where b.train<>a.train and b.train in
(select train from test6 where station='西安') and b.id>a.id)
and a.train in (select train from test6 where station='重庆')) c
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns table
as
return(
select a.train,a.station from 表 a join(
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
)b on a.train=b.train and a.id between b.id1 and b.id2
)
go
--调用进行查询
select * from f_qry('重庆','广安')
--测试--测试数据
create table 表(id int identity(1,1),train char(4),station varchar(10))
insert 表 select '1003','重庆'
union all select '1003','北碚'
union all select '1003','广安'
union all select '1004','庆北'
union all select '1004','北碚'
union all select '1004','广安'
union all select '1005','广安'
union all select '1005','北碚'
union all select '1005','重庆'
union all select '1005','广安'
go--查询的函数
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns table
as
return(
select a.train,a.station from 表 a join(
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
)b on a.train=b.train and a.id between b.id1 and b.id2
)
go
--调用进行查询
select * from f_qry('重庆','广安')
go--删除测试
drop table 表
drop function f_qry/*--测试结果train station
----- ----------
1003 重庆
1003 北碚
1003 广安
1005 重庆
1005 广安(所影响的行数为 5 行)
--*/
create table 表(id int identity(1,1),train char(4),station varchar(10))insert 表 select '1003','重庆'
union all select '1003','北碚'
union all select '1003','广安'union all select '1004','庆北'
union all select '1004','北碚'
union all select '1004','广安'union all select '1005','广安'
union all select '1005','北碚'
union all select '1005','重庆'
union all select '1005','西安'go--查询的函数
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns table
as
return(
select a.train,a.station from 表 a join(
select train,id1=id,id2=(
select min(id) from 表 where station=@end_station
and train=a.train and id>a.id)
from 表 a
where station=@start_station and exists(
select 1 from 表 where station=@end_station
and train=a.train and id>a.id)
)b on a.train=b.train and a.id between b.id1 and b.id2
)
go
--调用进行查询
select * from f_qry('重庆','西安')
go--删除测试
drop table 表
drop function f_qry/*--测试结果train station
----- ----------
1003 重庆
1003 北碚
1003 广安
1005 重庆
1005 广安(所影响的行数为 5 行)
--*/我想要的查询结果是
station
----------
广安
我用你的方法,查询不到内容,可能是我没有研究明白,我再看看
麻烦你再帮我看看。
union all select '1005','西安'
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns table
as
return(
select a1.station
from 表 a1 join(
select id=max(id) from 表 a
where exists(select 1 from 表 where station=@start_station and train=a.train )
group by train
)b1 on a1.id=b1.id
join 表 a2 on a1.station=a2.station
join(
select id=min(id) from 表 a
where exists(select 1 from 表 where station=@end_station and train=a.train )
group by train
)b2 on a2.id=b2.id
group by a1.station
)
go--调用进行查询
select * from f_qry('重庆','西安')
create table 表(id int identity(1,1),train char(4),station varchar(10))
insert 表 select '1003','重庆'
union all select '1003','北碚'
union all select '1003','广安'
union all select '1004','庆北'
union all select '1004','北碚'
union all select '1004','广安'
union all select '1005','广安'
union all select '1005','北碚'
union all select '1005','重庆'
union all select '1005','西安'
go--查询的函数
create function f_qry(
@start_station varchar(10),
@end_station varchar(10)
)returns table
as
return(
select a1.station
from 表 a1 join(
select id=max(id) from 表 a
where exists(select 1 from 表 where station=@start_station and train=a.train )
group by train
)b1 on a1.id=b1.id
join 表 a2 on a1.station=a2.station
join(
select id=min(id) from 表 a
where exists(select 1 from 表 where station=@end_station and train=a.train )
group by train
)b2 on a2.id=b2.id
group by a1.station
)
go--调用进行查询
select * from f_qry('重庆','西安')
go--删除测试
drop table 表
drop function f_qry/*--测试结果station
----------
广安(所影响的行数为 1 行)
--*/