求查询语句。

--> 测试数据：#a

if object_id('tempdb.dbo.#a') is not null drop table #a

create table #a(id int, bid varchar(8))

insert into #a

select 1, '1,2,4' union all

select 2, '2,3'

--> 测试数据：#b

if object_id('tempdb.dbo.#b') is not null drop table #b

create table #b(id int, name varchar(8))

insert into #b

select 1, 't1' union all

select 2, 't2' union all

select 3, 't3' union all

select 4, 't4'



select *, bname=stuff((select ','+name from #b where charindex(','+ltrim(id)+',',','+a.bid+',')>0 for xml path('')),1,1,'') from #a as a

/*

id          bid      bname

----------- -------- --------

1           1,2,4    t1,t2,t4

2           2,3      t2,t3

*/