求SQL语句 合并重复记录，并累加其中一个字段的值
a b c d e
0 1/26/2011 70 501262720 峨山县福兴副食品批发部 232256959 70 23225695970
2 1/26/2011 70 501262720 峨山县福兴副食品批发部 232256959 70 23225695970
5 1/26/2011 70 501262720 峨山县福兴副食品批发部 232256959 70 23225695970
125 1/26/2011 70 501262720 峨山县福兴副食品批发部 232256959 70 23225695970
字段E是ID但没有设置主键，我希望通过ID将重复的记录找出来并累积A列的值，形成一条记录，谢谢。
------解决方案--------------------
select b,c,d,e,sum(a)a
from tb
group by b,c,d,e
------解决方案--------------------
- SQL code
/*标题：按某字段合并字符串之一(简单合并)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-11-06地点：广东深圳描述：将如下形式的数据按id字段合并value字段。id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id value------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）*/--1、sql2000中只能用自定义的函数解决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 varchar(10)) returns varchar(1000)asbegin declare @str varchar(1000) select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id return @strendgo--调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb--2、sql2005中的方法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')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb--3、使用游标合并数据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')godeclare @t table(id int,value varchar(100))--定义结果集表变量--定义游标并进行合并处理declare my_cursor cursor local forselect id , value from tbdeclare @id_old int , @id int , @value varchar(10) , @s varchar(100)open my_cursorfetch my_cursor into @id , @valueselect @id_old = @id , @s=''while @@FETCH_STATUS = 0begin if @id = @id_old select @s = @s + ',' + cast(@value as varchar) else begin insert @t values(@id_old , stuff(@s,1,1,'')) select @s = ',' + cast(@value as varchar) , @id_old = @id end fetch my_cursor into @id , @valueENDinsert @t values(@id_old , stuff(@s,1,1,''))close my_cursordeallocate my_cursorselect * from @tdrop table tb
------解决方案--------------------
- SQL code
select sum(a) as a,max(b),max(c),max(d),e from 表where e in (select e from 表 group by e having count(*)>1)group by e