(升级版)一个不算难也不算简单的问题！希望高手可以看下！

Question

先分解,再合并.分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华  2007-12-23  广东深圳)/*问题描述
tba
ID  classid   name
1     1,2,3   西服 
2     2,3     中山装
3     1,3     名裤
tbb 
id   classname
1     衣服
2     上衣
3     裤子我得的结果是
id   classname            name
1     衣服,上衣,裤子      西服 
2     上衣，裤子          中山装
3     衣服，裤子          名裤
*/-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go--第1种方法，创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end
go 
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id          classid       name       
----------- ------------- ---------- 
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
（所影响的行数为 3 行）
*/--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID          classid              name       
----------- -------------------- ---------- 
1           衣服,上衣,裤子       西服
2           上衣,裤子            中山装
3           衣服,裤子            名裤
（所影响的行数为 3 行）
*/
drop table tba,tbb------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
goSELECT id , classname , name FROM
(
  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from 
  (
    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
  ) tbc , tbb where tbc.classid = tbb.id
  ) T
)A 
OUTER APPLY
(
  SELECT [classname]= STUFF(REPLACE(REPLACE((
    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from 
    (
      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
    ) tbc , tbb where tbc.classid = tbb.id
  ) N
  WHERE id = A.id and name = A.name
  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by iddrop table tba,tbb/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/

Answer 1

dawugui  你的方法有问题
因为我也是这方法做的
charindex 是判断里面有没有这个ID 没有排序
不然你可以试试我的表
然后看,56,67,65,   
我的结果是
,3,6,4, 
你的方法的结果会是,3,4,6,

Answer 2

create table tb1(n_id int,name nvarchar(20),class varchar(20))
insert tb1 select 252  ,  '小二'  ,   ',56,65,66,' 
insert tb1 select 367  ,  '小三'  ,   ',56,67,69,' 
insert tb1 select 555  ,  '小四'  ,   ',53,69,66,' 
insert tb1 select 785  ,  '小五'  ,   ',56,67,65,' 
insert tb1 select 786  ,  '小六'  ,   ',53,55,78,'create table tb2(c_id int,c_name nvarchar(20))
insert tb2 select 53  ,  '学生' 
insert tb2 select 55  ,  '老师' 
insert tb2 select 56  ,  '流氓' 
insert tb2 select 65  ,  '杀人犯' 
insert tb2 select 66  ,  '超人' 
insert tb2 select 67  ,  '蜘蛛侠' 
insert tb2 select 69  ,  '超级赛亚人' 
insert tb2 select 78  ,  '耶稣'
go
create table tb3(n_id int,name varchar(20))
insert tb3 select 1   ,  '学生' 
insert tb3 select 2   ,  '老师' 
insert tb3 select 3   ,  '流氓' 
insert tb3 select 4   ,  '杀人犯' 
insert tb3 select 5   ,  '超人' 
insert tb3 select 6   ,  '蜘蛛侠' 
insert tb3 select 7   ,  '超级赛亚人' 
insert tb3 select 8   ,  '耶稣' 
go
create function dbo.f_str(@class varchar(20))
  returns varchar(100)
as
begin
  select @class = replace(@class,','+rtrim(c_id)+',',','+rtrim(n_id)+',') from tb2,tb3   where c_name = name
  return @class
end
goselect class old_class,dbo.f_str(class) new_class,n_id into tb4
from tb1
select * from tb4
go
drop table tb1,tb2,tb3,tb4
drop function f_str/*
old_class            new_class                                                                                            n_id        
-------------------- ---------------------------------------------------------------------------------------------------- ----------- 
,56,65,66,           ,3,4,5,                                                                                              252
,56,67,69,           ,3,6,7,                                                                                              367
,53,69,66,           ,1,7,5,                                                                                              555
,56,67,65,           ,3,6,4,                                                                                              785
,53,55,78,           ,1,2,8,                                                                                              786
(5 行受影响)
*/

Answer 3

create table tb1(n_id varchar(10) , name varchar(10) , class varchar(20) )
insert into tb1 values('252', '小二', ',56,65,66,') 
insert into tb1 values('367', '小三', ',56,67,69,') 
insert into tb1 values('555', '小四', ',53,69,66,') 
insert into tb1 values('785', '小五', ',56,67,65,') 
insert into tb1 values('786', '小六', ',53,55,78,') 
create table tb2(c_id varchar(10), c_name varchar(10) )
insert into tb2 values('53', '学生') 
insert into tb2 values('55', '老师') 
insert into tb2 values('56', '流氓') 
insert into tb2 values('65', '杀人犯') 
insert into tb2 values('66', '超人') 
insert into tb2 values('67', '蜘蛛侠') 
insert into tb2 values('69', '超级赛亚人') 
insert into tb2 values('78', '耶稣') 
create table tb3(c_id varchar(10), c_name varchar(10)) 
insert into tb3 values('1', '学生') 
insert into tb3 values('2', '老师') 
insert into tb3 values('3', '流氓') 
insert into tb3 values('4', '杀人犯') 
insert into tb3 values('5', '超人') 
insert into tb3 values('6', '蜘蛛侠') 
insert into tb3 values('7', '超级赛亚人') 
insert into tb3 values('8', '耶稣') 
goselect t1.oldclass , newclass = ',' + t1.col1 + ',' + t2.col2 + ',' + t3.col3 + ',' , t1.n_id from 
(select tb1.class oldclass , tb1.n_id , col1 = (select c_id from tb3 where c_name = tb2.c_name and tb2.c_id = PARSENAME(replace(substring(tb1.class,2,len(tb1.class)-2),',','.'),3)) from tb1,tb2 where PARSENAME(replace(substring(tb1.class,2,len(tb1.class)-2),',','.'),3) = tb2.c_id) t1,
(select tb1.class oldclass , tb1.n_id , col2 = (select c_id from tb3 where c_name = tb2.c_name and tb2.c_id = PARSENAME(replace(substring(tb1.class,2,len(tb1.class)-2),',','.'),2)) from tb1,tb2 where PARSENAME(replace(substring(tb1.class,2,len(tb1.class)-2),',','.'),2) = tb2.c_id) t2,
(select tb1.class oldclass , tb1.n_id , col3 = (select c_id from tb3 where c_name = tb2.c_name and tb2.c_id = PARSENAME(replace(substring(tb1.class,2,len(tb1.class)-2),',','.'),1)) from tb1,tb2 where PARSENAME(replace(substring(tb1.class,2,len(tb1.class)-2),',','.'),1) = tb2.c_id) t3
where t1.oldclass = t2.oldclass and t1.oldclass = t3.oldclass and t1.n_id = t2.n_id and t1.n_id = t3.n_iddrop table tb1,tb2,tb3/*
oldclass             newclass                           n_id       
-------------------- ---------------------------------- ---------- 
,56,65,66,           ,3,4,5,                            252
,56,67,69,           ,3,6,7,                            367
,53,69,66,           ,1,7,5,                            555
,56,67,65,           ,3,6,4,                            785
,53,55,78,           ,1,2,8,                            786（所影响的行数为 5 行）
*/

Answer 4

谢谢JiangHongTao 的鼎力相助