SQL 轮换

表A：--------------------------------------------aID         bID----------- -----------107         31110         32111         33112         34113         35114         36117         40108         41表B：--------------------------------------------TaskIDs----------------------343532,33,40364131--------------------------------------------现在表B的存储的是表A中的bID如何将表B里的信息根据表A的对应关系全部替换成aID替换后的结果应该如下：表B中的TaskIDs是表A中ID的集合（一个或多个，中间以","分隔）--------------------------------------------TaskIDs----------------------112113110,111,117114108107

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    aID INT,    bID INT)GOINSERT INTO tbaSELECT 107,31 UNIONSELECT 110,         32 UNIONSELECT 111,         33 UNIONSELECT 112,         34 UNIONSELECT 113,         35 UNIONSELECT 114,         36 UNIONSELECT 117,         40 UNIONSELECT 108,         41GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')BEGIN    DROP TABLE tbbENDGOCREATE TABLE tbb(    TaskIDs VARCHAR(100))GOINSERT INTO tbbSELECT '34' UNION SELECT '35' UNION SELECT '32,33,40' UNION SELECT '36' UNION SELECT '41' UNION SELECT '31'GOWITH t AS(select    stuff(( select ','+ RTRIM(aID)                 from tba AS A                where CHARINDEX(RTRIM(bID),TaskIDs) > 0 for xml path('')),1,1,'') as Name,                TaskIDsfrom tbb AS B)UPDATE tbb SET tbb.TaskIDs = Name FROM t WHERE tbb.TaskIDs = t.TaskIDsSELECT * FROM tbb
------解决方案--------------------
SQL code
--分解字符串包含的信息值后然后合并到另外一表的信息--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)/*问题描述tbaID  classid   name1     1,2,3   西服 2     2,3     中山装3     1,3     名裤tbb id   classname1     衣服2     上衣3     裤子我得的结果是id   classname            name1     衣服,上衣,裤子      西服 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)asbegin  declare @str varchar(1000)  set @str=''  select @[email protected]+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',',[email protected]+',')>0  return stuff(@str,1,1,'')endgo select id,classid=dbo.f_hb(classid),name from tbadrop function f_hb/*id          classid       name       ----------- ------------- ---------- 1           衣服,上衣,裤子 西服2           上衣,裤子      中山装3           衣服,裤子      名裤（所影响的行数为 3 行）*/--第2种方法.updatewhile(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))update tbaset classid= replace(classid,tbb.id,tbb.classname)from tbbwhere charindex(tbb.id,tba.classid)>0select * from tba/*ID          classid              name       ----------- -------------------- ---------- 1           衣服,上衣,裤子       西服2           上衣,裤子            中山装3           衣服,裤子            名裤（所影响的行数为 3 行）*/drop table tba,tbb