多对多的关系数据中,如何从多得到一(第一个匹配),求sql

[lianyiyong]

table :FundingSource

contractkey FundingCodeKey
----------- --------------------------------------------------
3096 155
3098 156
3098 157
3100 162
3105 165
3106 161
3101 158
3103 166
3109 169
3110 170
3110 171 *(1)
3109 171 *(1)
3109 172
3110 172
3112 173
3113 174
3112 175
3113 175
3114 178
3115 179
3102 164
3102 180

在输入contractkey(3110,3109)时得到FundingCodeKey171
在输入contractkey(3112 ,3113)时得到FundingCodeKey175
contractkey和FundingCodeKey时多对多的关系.
当输入的contractkey的值和table中的数据关系匹配时得到FundingCodeKey

[danisluo]

select max(FundingCodeKey)
from FundingSource
where contractkey between (3110 and 3109)

[lw1a2]

得到一个contractkey范围内FundingCodeKey最大的？

[lianyiyong]

to: danisluo(沙砾)
这样好像不行把,between是3110 and 3109之间的数据,
更重要的是,我输入的数据可能会是一个或者更多

[lw1a2]

select top 1 FundingCodeKey
from FundingSource
where contractkey between 3110 and 3109

[lianyiyong]

我可能写的有问题,我输入的不是范围,而是具体的数字,使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,180

go


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
go

exec 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

[lianyiyong]

测试中

[lianyiyong]

TO: libin_ftsafe(子陌红尘)
高手啊,在这么短的时间可以分析清楚题目并给出正确的答案.
CSDN的福气啊!