急求删除数据库重复数据，用SQL语句

[lihao583240671]

id name sex nation mapinfo dress
3 陈冬生 男 NULL 30.636841,104.086533 南新街25号1栋1单元11号
3 陈冬生 男 NULL 30.636841,104.086533 南新街25号1栋1单元11号

[lihao583240671]

数据都相同
这个是表名oldpersoninfo

[AcHerat]

select distinct * into #t from tb
truncate table tb
insert into tb select * from #t
drop table tb

[zy35147972122]

你的数据就没有一个字段是不同的吗？这样没有标识的数据表设计的太不合理了吧

[hanger1212]

[Quote=引用 2 楼 acherat 的回复:]

SQL code

select distinct * into #t from tb
truncate table tb
insert into tb select * from #t
drop table tb
[/Quote]

+1

[AcHerat]

select distinct * into #t from tb
delete from tb   -- truncate 不产生LOG，建议用delete
insert into tb select * from #t
drop table tb

[wing7742]

DELETE t FROM (SELECT *,row=ROW_NUMBER() OVER (PARTITION BY id ORDER BY GETDATE()) FROM oldpersoninfo)t
WHERE t.row>1