一个关于replace的问题探讨
今天做数据，遇到一个这样的问题
表A，有两个字段，分别为A1，A2
数据: A1 | A2
司机 | 001，002，003，004
服务员 | 006，007
表B 有两个字段，分别为B1，B2
数据: B1 | B2
张三 | 001
李四 | 002
王五 | 003
许六 | 004
刘七 | 005
黄八 | 006
徐九 | 007
想要得到的结果
司机 | 张三,李四,王五,许六
服务员 | 刘七,黄八,徐九
这个语句还真的不知道要怎么写，想利用replace来写一个函数，不知道如何下手啊……
谁有好的方法，或者遇到类似的，虽然我知道写循环的话就能够得到我想要的效果，但总觉得牺牲了效率~~~
[解决办法]
/*SQL
;WITH c1(A1, A2)
AS
(
SELECT '司机','001，002，003，004'
UNION ALL
SELECT '服务员','006，007'
),
c2(B1,B2)
AS
(
SELECT '张三','001'
UNION ALL
SELECT '李四','002'
UNION ALL
SELECT '王五','003'
UNION ALL
SELECT '许六','004'
UNION ALL
SELECT '刘七','005'
UNION ALL
SELECT '黄八','006'
UNION ALL
SELECT '徐九','007'
)-- 准备数据 c1、c2视图
, c3 AS
(
SELECT c1.A2, c2.B1
FROM c1
JOIN c2 ON CHARINDEX(c2.b2, c1.a2) <> 0
)
SELECT DISTINCT c1.A1, STUFF(( SELECT ','+LTRIM(B1)
FROM c3 c
WHERE A2 = c3.A2 FOR XML PATH('')),1,1,'') 组合
FROM c3
JOIN c1 ON c3.A2 = c1.A2
*/
A1 组合
------ ----------------------------------
服务员 黄八,徐九
司机 张三,李四,王五,许六
(2 row(s) affected)
[解决办法]
--除了1楼的方法以外 还有以下几种办法：分解字符串包含的信息值后然后合并到另外一表的信息(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>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--------------------------------------sql server 2005中先分解tba中的classid,然后再合并classnamecreate 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, ''))Norder by iddrop table tba,tbb/*id classname name----------- -------------- ----------1 衣服,上衣,裤子 西服2 上衣,裤子 中山装3 衣服,裤子 名裤(3 行受影响)*/