A表(上千个数据条目)
id column_A
1 1,2,3
2 3,4
3 1,3B表(几百条数据)
id column_B
1 “神马”
2 "浮云"
3 "次奥"
4 "火车票"结果需要
id column_A column_B
1 1,2,3 "神马","浮云","次奥"
2 3,4 "次奥","火车票"
3 1,3 "神马","次奥"
.....
.....
.....谢谢帮忙的朋友,祝顺利买到返程火车票。多表子查询
id column_A
1 1,2,3
2 3,4
3 1,3B表(几百条数据)
id column_B
1 “神马”
2 "浮云"
3 "次奥"
4 "火车票"结果需要
id column_A column_B
1 1,2,3 "神马","浮云","次奥"
2 3,4 "次奥","火车票"
3 1,3 "神马","次奥"
.....
.....
.....谢谢帮忙的朋友,祝顺利买到返程火车票。多表子查询
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([id] int,[column_A] varchar(5))
insert #TA
select 1,'1,2,3' union all
select 2,'3,4' union all
select 3,'1,3'if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([id] int,[column_B] varchar(8))
insert #TB
select 1,'“神马”' union all
select 2,'"浮云"' union all
select 3,'"次奥"' union all
select 4,'"火车票"'
--------------开始查询--------------------------
;with cet as
(
SELECT a.* , SUBSTRING(a.column_A, number, CHARINDEX(',', a.column_A + ',', number) - number) AS element ,number
FROM #Ta a
JOIN master..spt_values
ON number <= LEN(a.column_A) and type='p'
AND SUBSTRING(',' + a.column_A, number, 1) = ','
and SUBSTRING(a.column_A, number, CHARINDEX(',', a.column_A + ',', number) - number) <>''
)
select d.id, stuff((select ','+b.column_B from cet a, #TB b
where a.element = b.id and a.id = d.id
order by a.id,a.number
for xml path('') ) ,1,1,'')d
from #TA d
----------------结果----------------------------
/*
id d
----------- --------------------------------
1 “神马”,"浮云","次奥"
2 "次奥","火车票"
3 “神马”,"次奥"(3 行受影响)
*/
with tb(id, column_A) as (
select 1, '1,2,3' union all
select 2, '3,4' union all
select 3, '1,3')
,tc(id, column_B) as (
select 1, '"神马"' union all
select 2, '"浮云"' union all
select 3, '"次奥"' union all
select 4, '"火车票"')
,td as(
select id,SUBSTRING(column_A,number,CHARINDEX(',',column_a+',',number)-number) b
from tb,master..spt_values where type='p' and number>0 and SUBSTRING(','+column_A,number,1)=',')
,te as (select td.id,column_b from td,tc where td.b=tc.id)
,tf as (select distinct id,stuff((select ','+column_B from te where id=a.id for xml path('')),1,1,'')c from te a)
select tb.id,column_a,c from tb,tf where tb.id=tf.id
create table #tb1(id int, column_A varchar(max))
insert into #tb1
select 1, '1,2,3' union all
select 2, '3,4' union all
select 3, '1,3'
create table #tb2(id int,column_B varchar(max))
insert into #tb2
select 1, '"神马"' union all
select 2, '"浮云"' union all
select 3, '"次奥"' union all
select 4, '"火车票"'
update t set column_a=
stuff((select ','+t2.column_b from #tb2 as t2 where charindex(','+rtrim(t2.id)+',',','+rtrim(t.column_A)+',')>0 for xml path(''))
,1,1,'') from #tb1 as t
select * from #tb1
drop table #tb1,#tb2
with a as(
select a.id,column_A,column_B
from a
join b on a.charindex(b.id,column_A) > 0)
select z.id,z.column_A,left(column_B,len(column_B)-1)column_B
(select a.id,a.column_A
,(select column_B+',' as column_B
from a as b
where a.id = b.id and a.column_A = b.column_A
for xml path(''))column_B
from a
group by a.id,a.column_A)z
--原始数据
with a as(
select 1 as id,'1,2,3' as column_A
union all
select 2,'3,4'
union all
select 3,'1,3'
)
, b as
(
select 1 as id,'神马' as column_B
union all
select 2,'浮云'
union all
select 3,'次奥'
union all
select 4,'火车票'
)
-------
, y as(
select a.id,column_A,column_B
from a
join b on charindex(convert(varchar(2),b.id),column_A) > 0)
select z.id,z.column_A,left(column_B,len(column_B)-1)column_B
from
(select y.id,y.column_A
,(select column_B+','
from y as c
where y.id = c.id and y.column_A = c.column_A
for xml path(''))column_B
from y
group by y.id,y.column_A)z
--结果
id column_A column_B
----------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2,3 神马,浮云,次奥
2 3,4 次奥,火车票
3 1,3 神马,次奥
(3 row(s) affected)