to: danisluo(沙砾) 这样好像不行把,between是3110 and 3109之间的数据, 更重要的是,我输入的数据可能会是一个或者更多
select top 1 FundingCodeKey from FundingSource where contractkey between 3110 and 3109
我可能写的有问题,我输入的不是范围,而是具体的数字,使contractkey匹配
create table FundingSource(contractkey int,FundingCodeKey int) insert into FundingSource select 3096,155 insert into FundingSource select 3098,156 insert into FundingSource select 3098,157 insert into FundingSource select 3100,162 insert into FundingSource select 3105,165 insert into FundingSource select 3106,161 insert into FundingSource select 3101,158 insert into FundingSource select 3103,166 insert into FundingSource select 3109,169 insert into FundingSource select 3110,170 insert into FundingSource select 3110,171 insert into FundingSource select 3109,171 insert into FundingSource select 3109,172 insert into FundingSource select 3110,172 insert into FundingSource select 3112,173 insert into FundingSource select 3113,174 insert into FundingSource select 3112,175 insert into FundingSource select 3113,175 insert into FundingSource select 3114,178 insert into FundingSource select 3115,179 insert into FundingSource select 3102,164 insert into FundingSource select 3102,180go create procedure sp_test(@contractkey varchar(100)) as begin declare @s varchar(8000) set @s = '' while charindex(',',@contractkey)>0 begin set @s = @s + ' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+left(@contractkey,charindex(',',@contractkey)-1)+')' set @contractkey = stuff(@contractkey,1,charindex(',',@contractkey),'') end set @s = @s + ' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+@contractkey+')' set @s = 'select top 1 a.FundingCodeKey from FundingSource a where '+stuff(@s,1,5,'') print @s exec(@s) end goexec sp_test '3110,3109' exec sp_test '3112,3113'drop procedure sp_test drop table FundingSource
--生成测试数据 create table FundingSource(contractkey int,FundingCodeKey int) insert into FundingSource select 3096,155 insert into FundingSource select 3098,156 insert into FundingSource select 3098,157 insert into FundingSource select 3100,162 insert into FundingSource select 3105,165 insert into FundingSource select 3106,161 insert into FundingSource select 3101,158 insert into FundingSource select 3103,166 insert into FundingSource select 3109,169 insert into FundingSource select 3110,170 insert into FundingSource select 3110,171 insert into FundingSource select 3109,171 insert into FundingSource select 3109,172 insert into FundingSource select 3110,172 insert into FundingSource select 3112,173 insert into FundingSource select 3113,174 insert into FundingSource select 3112,175 insert into FundingSource select 3113,175 insert into FundingSource select 3114,178 insert into FundingSource select 3115,179 insert into FundingSource select 3102,164 insert into FundingSource select 3102,180 go--创建存储过程 create procedure sp_test(@contractkey nvarchar(100),@FundingCodeKey int output) as begin declare @s nvarchar(4000) set @s = N'' while charindex(',',@contractkey)>0 begin set @s = @s + N' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+left(@contractkey,charindex(',',@contractkey)-1)+N')' set @contractkey = stuff(@contractkey,1,charindex(',',@contractkey),'') end set @s = @s + N' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+@contractkey+N')' set @s = N'select top 1 @FundingCodeKey=a.FundingCodeKey from FundingSource a where '+stuff(@s,1,5,'') --print @s exec sp_executesql @s,N'@FundingCodeKey int out',@FundingCodeKey out end go--执行存储过程,并查看结果 declare @FundingCodeKey int exec sp_test '3110,3109',@FundingCodeKey output print @FundingCodeKey -- /* 171 */exec sp_test '3112,3113',@FundingCodeKey output print @FundingCodeKey -- /* 175 */ go--删除测试环境 drop procedure sp_test drop table FundingSource go
TO: libin_ftsafe(子陌红尘) 高手啊,在这么短的时间可以分析清楚题目并给出正确的答案. CSDN的福气啊!
这样好像不行把,between是3110 and 3109之间的数据,
更重要的是,我输入的数据可能会是一个或者更多
from FundingSource
where contractkey between 3110 and 3109
insert into FundingSource select 3096,155
insert into FundingSource select 3098,156
insert into FundingSource select 3098,157
insert into FundingSource select 3100,162
insert into FundingSource select 3105,165
insert into FundingSource select 3106,161
insert into FundingSource select 3101,158
insert into FundingSource select 3103,166
insert into FundingSource select 3109,169
insert into FundingSource select 3110,170
insert into FundingSource select 3110,171
insert into FundingSource select 3109,171
insert into FundingSource select 3109,172
insert into FundingSource select 3110,172
insert into FundingSource select 3112,173
insert into FundingSource select 3113,174
insert into FundingSource select 3112,175
insert into FundingSource select 3113,175
insert into FundingSource select 3114,178
insert into FundingSource select 3115,179
insert into FundingSource select 3102,164
insert into FundingSource select 3102,180go
create procedure sp_test(@contractkey varchar(100))
as
begin
declare @s varchar(8000)
set @s = ''
while charindex(',',@contractkey)>0
begin
set @s = @s + ' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+left(@contractkey,charindex(',',@contractkey)-1)+')'
set @contractkey = stuff(@contractkey,1,charindex(',',@contractkey),'')
end
set @s = @s + ' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+@contractkey+')'
set @s = 'select top 1 a.FundingCodeKey from FundingSource a where '+stuff(@s,1,5,'')
print @s
exec(@s)
end
goexec sp_test '3110,3109'
exec sp_test '3112,3113'drop procedure sp_test
drop table FundingSource
create table FundingSource(contractkey int,FundingCodeKey int)
insert into FundingSource select 3096,155
insert into FundingSource select 3098,156
insert into FundingSource select 3098,157
insert into FundingSource select 3100,162
insert into FundingSource select 3105,165
insert into FundingSource select 3106,161
insert into FundingSource select 3101,158
insert into FundingSource select 3103,166
insert into FundingSource select 3109,169
insert into FundingSource select 3110,170
insert into FundingSource select 3110,171
insert into FundingSource select 3109,171
insert into FundingSource select 3109,172
insert into FundingSource select 3110,172
insert into FundingSource select 3112,173
insert into FundingSource select 3113,174
insert into FundingSource select 3112,175
insert into FundingSource select 3113,175
insert into FundingSource select 3114,178
insert into FundingSource select 3115,179
insert into FundingSource select 3102,164
insert into FundingSource select 3102,180
go--创建存储过程
create procedure sp_test(@contractkey nvarchar(100),@FundingCodeKey int output)
as
begin
declare @s nvarchar(4000)
set @s = N''
while charindex(',',@contractkey)>0
begin
set @s = @s + N' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+left(@contractkey,charindex(',',@contractkey)-1)+N')'
set @contractkey = stuff(@contractkey,1,charindex(',',@contractkey),'')
end
set @s = @s + N' and exists(select 1 from FundingSource where FundingCodeKey=a.FundingCodeKey and contractkey='+@contractkey+N')'
set @s = N'select top 1 @FundingCodeKey=a.FundingCodeKey from FundingSource a where '+stuff(@s,1,5,'')
--print @s
exec sp_executesql @s,N'@FundingCodeKey int out',@FundingCodeKey out
end
go--执行存储过程,并查看结果
declare @FundingCodeKey int
exec sp_test '3110,3109',@FundingCodeKey output
print @FundingCodeKey
--
/*
171
*/exec sp_test '3112,3113',@FundingCodeKey output
print @FundingCodeKey
--
/*
175
*/
go--删除测试环境
drop procedure sp_test
drop table FundingSource
go
高手啊,在这么短的时间可以分析清楚题目并给出正确的答案.
CSDN的福气啊!