declare @str int set @str=1 select id,(case when parsename(retwpid,1)=@str then parsename(retamount,1) when parsename(retwpid,2)=@str then parsename(retamount,2) when parsename(retwpid,3)=@str then parsename(retamount,3) end) from tb /* id ----------- - 3 3 3 1
create table tb(id int,retwpid varchar(10),retamount varchar(10)) insert into tb select 3,'12.1.10','1.3.4' union all select 3,'1.4.5','1.2.3' declare @str int set @str=1 select id,(case when parsename(retwpid,1)=@str then parsename(retamount,1) when parsename(retwpid,2)=@str then parsename(retamount,2) when parsename(retwpid,3)=@str then parsename(retamount,3) end) from tb /* id ----------- - 3 3 3 1
create table T3(wpid int, retwpid nvarchar(200),retamount nvarchar(200)) insert T3 select 3 ,'12,1,10', '1,3,4' union all select 4 ,'1,4,5', '1,2,3'create function TestF (@id nvarchar(10), @retwpid nvarchar(200), @retamount nvarchar(200) ) returns nvarchar(10) as begin declare @r nvarchar(10) set @r='' declare @t table(retwpid nvarchar(10),retamount nvarchar(10)) insert @t select w.col as retwpid,a.col as retamount from dbo.m_split(@retwpid,',') as w join dbo.m_split(@retamount,',') as a on a.id=w.id select @r=retamount from @t where retwpid=@id RETURN @r endcreate function [dbo].[m_split](@c varchar(2000),@split varchar(2)) --把字符串截開,生成一個單列的表 returns @t table(id int,col varchar(200)) as begin declare @i as int set @i=1 while(charindex(@split,@c)<>0) begin insert @t(id,col) values (@i,substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') set @i=@i+1 end insert @t(id,col) values (@i,@c) return enddeclare @rid as nvarchar(10) set @rid='12' --这是你说传的一个值 select wpid,dbo.TestF(@rid,retwpid,retamount) as retamount from T3
3 | 12,1,10 | 1,3,4
4 | 1,4,5 | 1,2,3
现在求一条语句,给retwpid传一个值,能返回“,”相对应的retamount
第一行显示3
第二行显示1
因为1在第一行位置是2,那么retamount第一行2的位置是3 ,所以显示3
第二行的位置在一,retamount第二行1的位置是1,所以显示1
set @str=1
select id,(case when parsename(retwpid,1)=@str then parsename(retamount,1)
when parsename(retwpid,2)=@str then parsename(retamount,2)
when parsename(retwpid,3)=@str then parsename(retamount,3) end)
from tb
/*
id
----------- -
3 3
3 1
insert into tb
select 3,'12.1.10','1.3.4' union all
select 3,'1.4.5','1.2.3'
declare @str int
set @str=1
select id,(case when parsename(retwpid,1)=@str then parsename(retamount,1)
when parsename(retwpid,2)=@str then parsename(retamount,2)
when parsename(retwpid,3)=@str then parsename(retamount,3) end)
from tb
/*
id
----------- -
3 3
3 1
这样传进来的值就有问题了。
如果我的retwpid 不确定有多少,有可能最多有20或者30个
比如retwpid =1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
那么怎么办呢,感谢
create table T3(wpid int, retwpid nvarchar(200),retamount nvarchar(200))
insert T3 select 3 ,'12,1,10', '1,3,4' union all
select 4 ,'1,4,5', '1,2,3'create function TestF
(@id nvarchar(10),
@retwpid nvarchar(200),
@retamount nvarchar(200)
)
returns nvarchar(10)
as
begin
declare @r nvarchar(10)
set @r=''
declare @t table(retwpid nvarchar(10),retamount nvarchar(10))
insert @t select w.col as retwpid,a.col as retamount
from dbo.m_split(@retwpid,',') as w
join dbo.m_split(@retamount,',') as a on a.id=w.id
select @r=retamount from @t where retwpid=@id
RETURN @r
endcreate function [dbo].[m_split](@c varchar(2000),@split varchar(2)) --把字符串截開,生成一個單列的表
returns @t table(id int,col varchar(200))
as
begin
declare @i as int
set @i=1
while(charindex(@split,@c)<>0)
begin
insert @t(id,col) values (@i,substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
set @i=@i+1
end
insert @t(id,col) values (@i,@c)
return
enddeclare @rid as nvarchar(10)
set @rid='12' --这是你说传的一个值
select wpid,dbo.TestF(@rid,retwpid,retamount) as retamount from T3