各位大大别嫌弃分少啊,问一个两个表关联查找的问题!
有ainfo表
ID Name
1 aa
2 bb
3 cc
4 dd
有binfo表
ID Name ainfoIDs
1 abcd 1,2,3
2 aaaa 2,3,4
3 idid 1,2
请问怎么将binfo表中ainfoIDs字段中的ID用ainfo表中的Name替换.显示成下面效果:binfo表
ID Name ainfoIDs
1 abcd aa,bb,cc
2 aaaa bb,cc,dd
3 idid aa,bb
------解决方案--------------------create table ainfo(
id varchar(10),
name varchar(10))
create table binfo(
id varchar(10),
name varchar(10),
aifoids varchar(60))
insert ainfo
select '1 ', 'aa '
union all
select '2 ', 'bb '
union all
select '3 ', 'cc '
union all
select '4 ', 'dd '
insert binfo
select '1 ', 'abcd ', '1,2,3 '
union all
select '2 ', 'aaaa ', '2,3,4 '
union all
select '3 ', 'idid ', '1,2 '
select * into tmp_ from binfo/* 用临时表试试*/
while(exists (select * from tmp_,ainfo where charindex(ainfo.id,tmp_.aifoids) > 0))
update tmp_
set aifoids= replace(aifoids,ainfo.id,ainfo.name)
from ainfo
where charindex(ainfo.id,tmp_.aifoids)> 0
select * from tmp_
drop table ainfo
drop table binfo
drop table tmp_