合并相同项 求高手指点 最好贴下语句
id val
-----
1 门
1 men
2 们
2 门
3 大
3 da
4 打
4 da
--------结果
men 门,们
da 大,打
[解决办法]
是要把拼音相同的合并？
[解决办法]
合并列值原著：邹建改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳表结构，数据如下：id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id values------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)--1. 创建处理函数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')goCREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + value FROM tb WHERE id=@id RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tbdrop function dbo.f_str/*id value ----------- -----------1 aa,bb2 aaa,bbb,ccc（所影响的行数为 2 行）*/--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--创建一个合并的函数create function f_hb(@id int)returns varchar(8000)asbegin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(value as varchar) from tb where id = @id set @str = right(@str , len(@str) - 1) return(@str)Endgo--调用自定义函数得到结果：select distinct id ,dbo.f_hb(id) as value from tbdrop table tbdrop function dbo.f_hb/*id value ----------- -----------1 aa,bb2 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, ''))Ndrop table tb/*id values----------- -----------1 aa,bb2 aaa,bbb,ccc(2 行受影响)*/--SQL2005中的方法2create 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')goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')from tbgroup by id/*id values----------- --------------------1 aa,bb2 aaa,bbb,ccc(2 row(s) affected)*/drop table tb
[解决办法]
没什么规律，不好合并。
[解决办法]
create table #test(id int,val nvarchar(20))insert into #test values(1,'门')insert into #test values(1,'men')insert into #test values(2,'们')insert into #test values(2,'men')insert into #test values(3,'大')insert into #test values(3,'da')insert into #test values(4,'打')insert into #test values(4,'da')with t as(select min(a.val) as pinyin , max(a.val) as wenzi from #test a,#test b where a.val=b.val group by a.id )select pinyin,( select wenzi+' ' from t awhere a.pinyin=b.pinyin for xml path('') ) as wenzi from t b group by pinyin
[解决办法]
1、
先使用http://www.cnblogs.com/ret00100/archive/2010/08/06/1793725.html 下面的第二个函数求出每个汉字的拼音，然后再合并字符串（2楼的方法）
2、如果是按照6楼的格式的话，即第一行是汉字，下面一行是该汉字的拼音，那么按照6楼的即可