一个SQL问题

Question

我有一个表的字段为
A表
id value
1  1,2
2  2,3
3  1,3
另一个表是对应value的数据
B表
id name
1   书
2   电脑
3   笔记本我想得到
C表 
id value
1  书,电脑
2  电脑,笔记本
3  书,笔记本这种该怎么办呢？

Answer 1

--> 测试数据: #ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (id int,[value]  varchar(6) )
insert into #ta
select 1,'1,2' union all
select 2,'2,3' union all
select 3,'1,3'
--> 测试数据: #tB
if object_id('tempdb.dbo.#tB') is not null drop table #tB
go
create table #tB (id int,name varchar(6))
insert into #tB
select 1,'书' union all
select 2,'电脑' union all
select 3,'笔记本';with cte as
(
select a.id,b.name 
from #ta a,#tb b 
where charindex(','+ltrim(b.id)+',',','+a.[value]+',')>0
)select id ,
name=stuff( (select ','+name from cte where id=t.id for xml path('')),1,1,'')
from cte t
group by idid          name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           书,电脑
2           电脑,笔记本
3           书,笔记本(3 行受影响)

Answer 2

if not object_id('ta') is null
drop table ta
Go
Create table ta([id] int,[value] nvarchar(3))
Insert ta
select 1,N'1,2' union all
select 2,N'2,3' union all
select 3,N'1,3'
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[name] nvarchar(3))
Insert tb
select 1,N'书' union all
select 2,N'电脑' union all
select 3,N'笔记本'
Go
select ID,
       [value]=stuff((select ','+[name]
                      from tb b
                      where charindex(','+ltrim(ID)+',',','+a.[value]+',')>0
                      for xml path('')),1,1,'')       
from ta a
/*
ID          value
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           书,电脑
2           电脑,笔记本
3           书,笔记本(3 row(s) affected)
*/

Answer 3

use City;
go
if OBJECT_ID(N'A',N'U') is not null drop table A
go
if OBJECT_ID(N'B',N'U') is not null drop table B
go
create table A
(
 id int identity(1,1) primary key not null,
 value nvarchar(5)
)
go
create table B
(
 id int identity(1,1) primary key not null,
 name nvarchar(5)
)
go
--插入A表测试数据
insert into A
select '1,2' union all
select '2,3' union all
select '1,3'
go
--插入B表测试数据
insert into B
select '书' union all
select '电脑' union all
select '笔记本'
go
with cte1 as
(
 select ATable.AID,BTable.BID from
(
  select cast('<V>'+replace(value,',','</V><V>')+'</V>' as xml)as ROW,id as AID
  from A
)as ATable
outer apply 
(
  select 
  C.value('.','int') As BID
  from ATable.ROW.nodes('/V') T(C)
)as BTable
)
select distinct AID,
stuff
((select ','+name from B where id in
       (select BID from cte1 as twocte where onecte.AID=twocte.AID)
    for XML path('')),1,1,'')as myname from cte1 as onecte
drop table A
drop table B
/*(3 行受影响)(3 行受影响)
AID         myname
----------- --------------
1           书,电脑
2           电脑,笔记本
3           书,笔记本(3 行受影响)
*/