select distinct *
into #a
from tablenam
where username in
(select max(username)
from a
group by username
having (*)>1)delete a from a a,#a b where a.username=b.username and a.age=b.age insert into
a (usernaem,age)
select username,age from #a 不知道你的字段名,所以你参考一下以上的语句
into #a
from tablenam
where username in
(select max(username)
from a
group by username
having (*)>1)delete a from a a,#a b where a.username=b.username and a.age=b.age insert into
a (usernaem,age)
select username,age from #a 不知道你的字段名,所以你参考一下以上的语句
http://community.csdn.net/Expert/topic/3219/3219851.xml?temp=.5891992
取重复中的一条记录,放中间表。
删除原始表
从临时表中把数据插入原始表
delete from table1
insert table select * from #a
drop #a
table1 你的表名
Insdrt Table1 (id)
Select max(id), 广东省, 广东省, 广州市兴达盛电器有限公司, 同和京溪春兰花园
From Table Group by 广东省, 广东省, 广州市兴达盛电器有限公司, 同和京溪春兰花园Delete From Table Where id Not In (Select id Table1)
#a是INTO以后的临时表名。
into #a --不重复的放临时表#a
from yourtabledelete from yourtable --删除原始表 yourtableselect *
insert yourtable ---把临时表中的数据插进你的原始表yourtable
from #a drop #a --删除临时表yourtable 你的表名
如果表中数据量很大的话,这样做很慢的
add ID int identity(1,1)
go
delete from biao
where ID not in(select max(ID) from biao group by 字段1,字段2,...)
go
alter table biao
drop column ID
1、增加一字段fid,自动增量(区别每一条记录的不同)
2、写一条删除记录的sql语句。
delete from table where fid not in(select min(fid) from table group by fields1,fields2,..)
注:fields表示相同记录的字段