tb1
----------
name
aa
bb,cc,ff
bb,aa,dd
tb2
---------
id name
01 aa
03 bb
02 cc
23 ff
88 dd要求
tb1
-------
name
01
03,02,23
03,01,88
----------
name
aa
bb,cc,ff
bb,aa,dd
tb2
---------
id name
01 aa
03 bb
02 cc
23 ff
88 dd要求
tb1
-------
name
01
03,02,23
03,01,88
參照
from tb1 a
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1 (name varchar(22))
insert into #tb1
select 'aa' union all
select 'bb,cc,ff' union all
select 'bb,aa,dd'
--> 测试数据: #tb2
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2
go
create table #tb2 (id varchar(2),name varchar(4))
insert into #tb2
select '01','aa' union all
select '03','bb' union all
select '02','cc' union all
select '23','ff' union all
select '88','dd'alter table #tb1 add n int identity(1,1);with cte as
(
select b.*,a.n
from #tb1 a ,#tb2 b
where CHARINDEX(','+b.name+',',','+a.name+',')>0
)
update #tb1
set name=b.name
from #tb1 a,
(
select n,
name=STUFF((select ','+id from cte where n=t.n for XML path('')),1,1,'')
from cte t
group by n
)b
where a.n=b.n
select * from #tb1
name n
---------------------- -----------
01 1
03,02,23 2
01,03,88 3(3 row(s) affected)
use Tempdb
go
--> -->
if not object_id(N'TB1') is null
drop table TB1
Go
Create table TB1([name] nvarchar(8))
Insert TB1
select N'aa' union all
select N'bb,cc,ff' union all
select N'bb,aa,dd'
Go--> -->
if not object_id(N'TB2') is null
drop table TB2
Go
Create table TB2([id] nvarchar(2),[name] nvarchar(2))
Insert TB2
select N'01',N'aa' union all
select N'03',N'bb' union all
select N'02',N'cc' union all
select N'23',N'ff' union all
select N'88',N'dd'
Go
USE tempdb
GO
create function f_str(@Str nvarchar(1000))
returns nvarchar(1000)
as
begin
set @Str=','+@Str+','
select @Str=replace(@Str,','+Name+',',','+rtrim(ID)+',') --如果ID為字符時,可去掉rtrim
from tb2 where @Str like '%,'+Name+',%'
return substring(@Str,2,len(@Str)-2)
END
GOSELECT dbo.f_str([name]) FROM TB1
/*
01
03,02,23
03,01,88
*/
insert into tb1
select 'aa' union all
select 'bb,cc,ff' union all
select 'bb,aa,dd'
create table tb2 (id varchar(10),name varchar(10))
insert into tb2
select '01','aa' union all
select '03','bb' union all
select '02','cc' union all
select '23','ff' union all
select '88','dd' update a set name=stuff((select ','+id from tb2
where charindex(name+',',a.name+',')>0 for xml path('')),1,1,'')
from tb1 aselect * from tb1
/*
name
--------------------
01
03,02,23
01,03,88
create table t1
(
vol1 varchar(50)
)
insert into t1
select 'aa' union all
select 'bb,cc,ff' union all
select 'bb,aa,dd'
create table t2
(
id varchar(2),
vol2 varchar(50)
)
insert into t2
select '01', 'aa' union all
select '03', 'bb' union all
select '02', 'cc' union all
select '23', 'ff' union all
select '88', 'dd'update t1
set vol1=
(select stuff((select ','+id from t2 where charindex(','+vol2+',',','+t1.vol1+',')>0 for xml path('')),1,1,''))
from t1