--两条语句可以--1.先合计 update 表 set E=b.sumE from 表 a,( select A,B,C,D,E=max(E),sumE=sum(E) from 表 group by A,B,C,D )b where a.A=b.A and a.B=b.B and a.C=b.C and a.D=b.D and a.E=b.E--再删除 delete a from 表 a where exists( select * from 表 where a.A=A and a.B=B and a.C=C and a.D=D and a.E<E)
--如果只是要显示去掉重复的结果,一句可以select A,B,C,D,E=um(E) from 表 group by A,B,C,D
select A,B,C,D,SUM(E) AS E into 新表 from 旧表 group by A,B,C,D DROP TABLE 旧表 将新表名改成旧表名
select A,B,C,D,sum(E) as E into #t from 表 group by A,B,C,D --插入临时表 delete from 表 删除原表内容 insert into 表 select * from #t 更新原表内容
update 表 set E=b.sumE
from 表 a,(
select A,B,C,D,E=max(E),sumE=sum(E)
from 表
group by A,B,C,D
)b where a.A=b.A and a.B=b.B and a.C=b.C and a.D=b.D and a.E=b.E--再删除
delete a from 表 a
where exists(
select * from 表
where a.A=A and a.B=B and a.C=C and a.D=D and a.E<E)
from 表
group by A,B,C,D
DROP TABLE 旧表
将新表名改成旧表名
delete from 表 删除原表内容
insert into 表 select * from #t 更新原表内容