2008-06-04
如何删除表中重复的纪录
有test表如下：如何删除表中重复的纪录
pri name
111 aaa
222 bbb
333 ccc
111 aaa
222 bbb
333 ccc
方法一:使用distinct和临时表
select distinct * into temp from testtable
delete from testtable
insert into testtable select * from temp
方法二:添加标识列
select *from testtable
alter table testtable add newid int identity(1,1)
go
delete from testtable where exists(
select 1 from testtable a where a.newid>testtable .newid
and testtable.pri=a.pri and testtable.name=a.name)
go
alter table testtable drop column newid
go
另外一篇文章搜藏
http://blog.csdn.net/xiaojianpitt/archive/2007/11/29/1906959.aspx
评论排行榜