34,589
社区成员
发帖
与我相关
我的任务
分享
create table MainTable(Id int, Title varchar(20),SubId varchar(100))
insert into MainTable
select 1 ,'A', 0 union all
select 2 ,'B', 0 union all
select 3 ,'C', 0 union all
select 4 ,'D', 0 union all
select 5 ,'E', 0
create table SubTable(Id int,Description varchar(20))
insert into SubTable
select 1 ,'ABC' union all
select 2 ,'AB' union all
select 3 ,'CD' union all
select 4 ,'EA'
go
--修改数据
update MainTable
set SubId =cast(MainTable.SubId as varchar) +
(select ','+ CAST(t.ID as varchar)
from SubTable t
where t.Description like '%'+MainTable.Title+'%'
for xml path('')
)
--查询修改后的数据
select * from MainTable
/*
ID title SubId
1 A 0,1,2,4
2 B 0,1,2
3 C 0,1,3
4 D 0,3
5 E 0,4
*/
create table MainTable(Id int, Title varchar(20),SubId int)
insert into MainTable
select 1 ,'A', 0 union all
select 2 ,'B', 0 union all
select 3 ,'C', 0 union all
select 4 ,'D', 0 union all
select 5 ,'E', 0
create table SubTable(Id int,Description varchar(20))
insert into SubTable
select 1 ,'ABC' union all
select 2 ,'AB' union all
select 3 ,'CD' union all
select 4 ,'EA'
go
select distinct
ID,
title,
cast(m.SubId as varchar) +
(select ','+ CAST(t.ID as varchar)
from SubTable t
where t.Description like '%'+m.Title+'%'
for xml path('')
) as SubId
from MainTable m
/*
ID title SubId
1 A 0,1,2,4
2 B 0,1,2
3 C 0,1,3
4 D 0,3
5 E 0,4
*/
select 1 Id,'A' Title,convert(varchar(max), '0') SubId
into #MainTable
union all select 2 ,'B','0'
union all select 3 ,'C','0'
union all select 4 ,'D','0'
union all select 5 ,'E','0'
select 1 id ,'ABC' Description
into #SubTable
union all select 2 ,'AB'
union all select 3 ,'CD'
union all select 4 ,'EA'
select *
from #MainTable
update b
set SubId=convert(varchar(20),subid) +(select ','+ convert(varchar(20),a.id) from #SubTable a where CHARINDEX(b.Title,a.Description,1)>0
for xml path('') )
from #MainTable b
select *
from #MainTable
result:
/*
1 A 0,1,2,4
2 B 0,1,2
3 C 0,1,3
4 D 0,3
5 E 0,4
*/
try this