sql 中outer apply 和XML 并用

create table a 
(
id int,
tag varchar(100)
)
create table b
(
id int,
description nvarchar(100)
)
go
insert into a 
select 1,'1,2,3' union all
select 2,'1,3' union all
select 3,'2,3'
go
insert into b
select 1,'拉面' union all
select 2,'方便面' union all
select 3,'牛肉面'
go

with cte as
(
select id,tag,valu from 
(
select id,tag, cast(  '<r><v>'+replace(tag,',','</v><v>')+'</v></r>' as xml) tagxml from a
) aa
outer apply
(
select t.c.value('.','nvarchar(100)')  [valu] from 
aa.tagxml.nodes('/r/v') as t(c)
)bb
),temp as
(
select cte.id,tag ,description from cte left join b on cte.valu = b.id
)
select id,tag,
description = (
stuff(
(select ','+ description from temp where a.id = id and a.tag = tag for xml path(''))
,1,1,'')
)
 from temp a group by id,tag

drop table a
drop table b

结果：

(3 行受影响)

(3 行受影响)
id          tag                                                                                                  description
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1,2,3                                                                                                拉面,方便面,牛肉面
2           1,3                                                                                                  拉面,牛肉面
3           2,3                                                                                                  方便面,牛肉面

(3 行受影响)