求助，实现以下替换功能-CSDN论坛

⋅求助，实现以下替换功能 更多帖子 关注私聊空间博客 acen_chen 本版专家分：482 结帖率 100%: 表T1
id  code
内容：
1  01,02
2  02,04,05
3  03
...

表T2
code  name
01    上海
02    北京
03    天津
04    广州
05    大连
....

查询结果
1  上海，北京
2  北京，广州，大连
3  天津
....

0 2010-03-19 10:43:46

回复数 12 只看楼主引用举报楼主

⋅求常用的发布--订阅的SQL巡检语句 ⋅服务启动账号为LocalSytem 如何做镜像 ⋅俺的帖子为啥被删除了 更多帖子 关注私聊空间博客 sql_sf 本版专家分：6675: ---2000
还是
2005
？

0 2010-03-19 10:44:37

只看TA 引用举报 #1 得分 0

⋅-----------------------------恭喜SQL SERVER的各位MVP--------------------- ⋅---------------对面的女孩看过来------------------------ ⋅------------------恭喜SQL版再诞生一位钻石DBA_Huangzj------------------------------ 更多帖子 关注私聊空间博客 --小F-- 本版专家分：313969 版主 名人 2012年荣获名人称号 进士 2011年总版技术专家分年内排行榜第四 2010年总版技术专家分年内排行榜第九 2009年总版技术专家分年内排行榜第八 金牌 2011年10月总版技术专家分月排行榜第一 银牌 2011年12月总版技术专家分月排行榜第二 2011年9月总版技术专家分月排行榜第二 2009年11月总版技术专家分月排行榜第二

合并列值 

--*******************************************************************************************

表结构，数据如下： 

id    value 

----- ------ 

1    aa 

1    bb 

2    aaa 

2    bbb 

2    ccc 



需要得到结果： 

id    values 

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

1      aa,bb 

2      aaa,bbb,ccc 

即：group by id, 求 value 的和（字符串相加） 



1. 旧的解决方法(在sql server 2000中只能用函数解决。) 

--=============================================================================

create table tb(id int, value varchar(10)) 

insert into tb values(1, 'aa') 

insert into tb values(1, 'bb') 

insert into tb values(2, 'aaa') 

insert into tb values(2, 'bbb') 

insert into tb values(2, 'ccc') 

go 

--1. 创建处理函数 

CREATE FUNCTION dbo.f_strUnite(@id int) 

RETURNS varchar(8000) 

AS 

BEGIN 

    DECLARE @str varchar(8000) 

    SET @str = '' 

    SELECT @str = @str + ',' + value FROM tb WHERE id=@id 

    RETURN STUFF(@str, 1, 1, '') 

END 

GO 

-- 调用函数 

SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id 

drop table tb 

drop function dbo.f_strUnite 

go

/* 

id          value      

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

1          aa,bb 

2          aaa,bbb,ccc 

（所影响的行数为 2 行） 

*/ 

--===================================================================================

2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 

create table tb(id int, value varchar(10)) 

insert into tb values(1, 'aa') 

insert into tb values(1, 'bb') 

insert into tb values(2, 'aaa') 

insert into tb values(2, 'bbb') 

insert into tb values(2, 'ccc') 

go 

-- 查询处理 

SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( 

        SELECT [values]= STUFF(REPLACE(REPLACE( 

            ( 

                SELECT value FROM tb N 

                WHERE id = A.id 

                FOR XML AUTO 

            ), ' <N value="', ','), '"/>', ''), 1, 1, '') 

)N 

drop table tb 



/* 

id          values 

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

1          aa,bb 

2          aaa,bbb,ccc 



(2 行受影响) 

*/ 



--SQL2005中的方法2 

create table tb(id int, value varchar(10)) 

insert into tb values(1, 'aa') 

insert into tb values(1, 'bb') 

insert into tb values(2, 'aaa') 

insert into tb values(2, 'bbb') 

insert into tb values(2, 'ccc') 

go 



select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 

from tb 

group by id 



/* 

id          values 

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

1          aa,bb 

2          aaa,bbb,ccc 



(2 row(s) affected) 



*/ 



drop table tb 



/*

标题：分拆列值1

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-11-20

地点：广东深圳

描述



有表tb, 如下:

id          value

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

1           aa,bb

2           aaa,bbb,ccc

欲按id,分拆value列, 分拆后结果如下:

id          value

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

1           aa

1           bb

2           aaa

2           bbb

2           ccc

*/



--1. 旧的解决方法(sql server 2000)

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 



SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)

FROM tb A, # B

WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','



DROP TABLE #



--2. 新的解决方法(sql server 2005) 

create table tb(id int,value varchar(30))

insert into tb values(1,'aa,bb')

insert into tb values(2,'aaa,bbb,ccc')

go

SELECT A.id, B.value

FROM(

    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb

)A

OUTER APPLY(

    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)

)B



DROP TABLE tb



/*

id          value

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

1           aa

1           bb

2           aaa

2           bbb

2           ccc



(5 行受影响)

*/

0 2010-03-19 10:45:56

只看TA 引用举报 #2 得分 80

⋅【我都快忘了还有csdn这一玩意】，路过散散分 ⋅凑凑热闹,很久不写东西,手痒了 ⋅【锅路过时,轻轻的说一句】【其实锅真的没有被隔离。】 更多帖子 关注私聊空间博客 playwarcraft 本版专家分：42823: 有请那谁谁谁，来贴个龟龟的标准答案。。。

0 2010-03-19 10:46:24

只看TA 引用举报 #3 得分 0

⋅求常用的发布--订阅的SQL巡检语句 ⋅服务启动账号为LocalSytem 如何做镜像 ⋅俺的帖子为啥被删除了 更多帖子 关注私聊空间博客 sql_sf 本版专家分：6675: http://topic.csdn.net/u/20100311/13/9bb4ed06-a31e-4997-a049-f33e5f79d525.html
参考此帖

0 2010-03-19 10:46:39

只看TA 引用举报 #4 得分 20

⋅HTML代码在 outlook 中不能正常显示 ⋅ASP.NET 将后台返回得到的JSON 转换为数组 ⋅sqlserver 中使用透明数据加密(Transparent Data Encryption)加密数据库，让你的数据库更加安全 更多帖子 关注私聊空间博客 ws_hgo 本版专家分：38964 蓝花 2010年9月 MS-SQL Server大版内专家分月排行榜第三: 0 2010-03-19 10:47:09

只看TA 引用举报 #5 得分 0

⋅-----------------------------恭喜SQL SERVER的各位MVP--------------------- ⋅---------------对面的女孩看过来------------------------ ⋅------------------恭喜SQL版再诞生一位钻石DBA_Huangzj------------------------------ 更多帖子 关注私聊空间博客 --小F-- 本版专家分：313969 版主 名人 2012年荣获名人称号 进士 2011年总版技术专家分年内排行榜第四 2010年总版技术专家分年内排行榜第九 2009年总版技术专家分年内排行榜第八 金牌 2011年10月总版技术专家分月排行榜第一 银牌 2011年12月总版技术专家分月排行榜第二 2011年9月总版技术专家分月排行榜第二 2009年11月总版技术专家分月排行榜第二

---贴错

分解字符串包含的信息值后然后合并到另外一表的信息

(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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','裤子')

go



SELECT 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 id



drop table tba,tbb



/*

id          classname      name

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

1           衣服,上衣,裤子 西服

2           上衣,裤子      中山装

3           衣服,裤子      名裤

(3 行受影响)

*/

0 2010-03-19 10:47:16

只看TA 引用举报 #6 得分 0

acen_chen 本版专家分：482: 2k,谢谢，我试试

0 2010-03-19 10:47:18

只看TA 引用举报 #7 得分 0

⋅通知:尽最大努力让小F和晴天本月拿牌,本人这个月剩余几天不抢分. ⋅感谢管理员退还我被盗用的另外一千分.谢谢! ⋅感谢管理员退还我的可用分.不过仍有一个疑问. 更多帖子 关注私聊空间博客 dawugui 本版专家分：440973 名人年度总版至少三次排名前十即授予名人勋章 榜眼 2008年总版技术专家分年内排行榜第二 进士 2010年总版技术专家分年内排行榜第四 2009年总版技术专家分年内排行榜第五 2007年总版技术专家分年内排行榜第六 金牌 2009年3月总版技术专家分月排行榜第一 2008年12月总版技术专家分月排行榜第一 2008年3月总版技术专家分月排行榜第一 2007年12月总版技术专家分月排行榜第一 2007年10月总版技术专家分月排行榜第一: 引用 3 楼 playwarcraft 的回复:
有请那谁谁谁，来贴个龟龟的标准答案。。。
哈哈,那我就不帖了.

0 2010-03-19 10:49:23

只看TA 引用举报 #8 得分 0

acen_chen 本版专家分：482: 结贴，给分

0 2010-03-19 10:52:10

只看TA 引用举报 #9 得分 0

⋅HTML代码在 outlook 中不能正常显示 ⋅ASP.NET 将后台返回得到的JSON 转换为数组 ⋅sqlserver 中使用透明数据加密(Transparent Data Encryption)加密数据库，让你的数据库更加安全 更多帖子 关注私聊空间博客 ws_hgo 本版专家分：38964 蓝花 2010年9月 MS-SQL Server大版内专家分月排行榜第三: 就这样啊

0 2010-03-19 10:53:06

只看TA 引用举报 #10 得分 0

⋅路过，散分！

⋅[散分] 许久不来，散个分，混个脸熟

⋅押宝啦！！10月29日凌晨零点英超切尔西对曼联，天王山之战

更多帖子

关注私聊空间博客

百年树人

本版专家分：96450

铜牌 2010年2月总版技术专家分月排行榜第三

微软MVP 2012年7月荣获微软MVP称号

红花 2010年2月 MS-SQL Server大版内专家分月排行榜第一
2009年5月 MS-SQL Server大版内专家分月排行榜第一

黄花 2012年6月 MS-SQL Server大版内专家分月排行榜第二
2010年8月 MS-SQL Server大版内专家分月排行榜第二

---测试数据---

if object_id('[T1]') is not null drop table [T1]

go

create table [T1]([id] int,[code] varchar(8))

insert [T1]

select 1,'01,02' union all

select 2,'02,04,05' union all

select 3,'03'

if object_id('[T2]') is not null drop table [T2]

go

create table [T2]([code] varchar(2),[name] varchar(4))

insert [T2]

select '01','上海' union all

select '02','北京' union all

select '03','天津' union all

select '04','广州' union all

select '05','大连'

 

---查询---

CREATE function [dbo].[ChangeName]

    (@SourceSql varchar(100),

    @StrSeprate varchar(10))

    returns varchar(200)

as     

begin   

     declare @ch as varchar(100)   

     declare @StrReturn as varchar(100)   

     set @SourceSql=@SourceSql+@StrSeprate

     set @StrReturn=''

     while(@SourceSql<>'')   

        begin   

           set @ch=left(@SourceSql,charindex(@StrSeprate,@SourceSql,1)-1)   

           select  @StrReturn=@StrReturn+name+',' from T2 where code=@ch

           set @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),'')   

       end   

    set @StrReturn = stuff(@StrReturn,len(@StrReturn),1,'')

    return @StrReturn

end

GO



select id,dbo.ChangeName(code,',') AS NAME from T1

/**

id          NAME

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

1           上海,北京

2           北京,广州,大连

3           天津



(3 行受影响)

**/

0 2010-03-19 10:53:19

只看TA 引用举报 #11 得分 0

⋅英中日三版本windowsXP[公司用] ⋅领航致远_李腾飞老师javascript_html_css视频教程 ⋅视频教程_传智播客spring2.5_avi 更多帖子 关注私聊空间博客 永生天地 本版专家分：72568 进士 2010年总版技术专家分年内排行榜第七 银牌 2010年7月总版技术专家分月排行榜第二 2010年6月总版技术专家分月排行榜第二 铜牌 2010年5月总版技术专家分月排行榜第三 红花 2010年8月 PowerBuilder大版内专家分月排行榜第一 2010年8月 MS-SQL Server大版内专家分月排行榜第一 2010年7月 MS-SQL Server大版内专家分月排行榜第一 2010年6月 PowerBuilder大版内专家分月排行榜第一 2010年6月 MS-SQL Server大版内专家分月排行榜第一 2010年5月 PowerBuilder大版内专家分月排行榜第一 2010年4月 PowerBuilder大版内专家分月排行榜第一 2009年10月 PowerBuilder大版内专家分月排行榜第一 2009年7月 PowerBuilder大版内专家分月排行榜第一: 晕

0 2010-03-19 10:58:50

只看TA 引用举报 #12 得分 0

求助，实现以下替换功能 [问题点数：100分，结帖人acen_chen]