比如它只是一个字段里的值,那么如何去取呢? A: IDSTR,OTHER 1,2,3,4 XXX B:ID NAME 1 AA 2 BB 3 CC 4 DD A---B通过A的IDSTR以及B的ID来连接。预期得到的结果是: AA,BB,CC,DD XXX
这个用动态SQL好做点,不过遇到B表数据比较多的情况下,性能不理想
create table #t1 (instr varchar(10) )insert into #t1 select '1,3,5' union all select '2,3,1'create table #t2 ( idx int, iname varchar(5) )insert into #t2 select 1,'红' union all select 2,'黄' union all select 3,'蓝' union all select 4,'白' union all select 5,'黑'declare @sql varchar(200),@i int,@j int select @i=1,@j=count(1) from #t2 set @sql='select 'while @i<=@j begin set @sql=@sql+'replace(' set @i=@i+1 endset @sql=@sql+'instr'select @sql=@sql+','+cast(idx as varchar(5))+','''+iname+''')' from #t2 set @sql=@sql+' from #t1'exec(@sql)--只实现了功能,动态的做法就是这样
1 AA
2 BB
3 CC
4 DD
1,2,3,4 XXX
B:ID NAME
1 AA
2 BB
3 CC
4 DD
A---B通过A的IDSTR以及B的ID来连接。预期得到的结果是:
AA,BB,CC,DD XXX
create table #t1
(instr varchar(10)
)insert into #t1
select '1,3,5'
union all
select '2,3,1'create table #t2
(
idx int,
iname varchar(5)
)insert into #t2
select 1,'红'
union all
select 2,'黄'
union all
select 3,'蓝'
union all
select 4,'白'
union all
select 5,'黑'declare @sql varchar(200),@i int,@j int
select @i=1,@j=count(1) from #t2
set @sql='select 'while @i<=@j
begin
set @sql=@sql+'replace('
set @i=@i+1
endset @sql=@sql+'instr'select @sql=@sql+','+cast(idx as varchar(5))+','''+iname+''')' from #t2
set @sql=@sql+' from #t1'exec(@sql)--只实现了功能,动态的做法就是这样