sql 删除数据库中的重复记录方法

我们分享了二个实现，来告诉你如何把数据表中重复记录的记录给删除只保留一条，这种方法风险性比较大，另一种方法利用临时表，这种方法相对上种要好些。

删除中的重复记录（且仅保留一条有效记录）示例-

代码如下

复制代码

create table A ( userID int identity(1,1), userName varchar(20), userPwd varchar(20), userEmail varchar(50) ) insert into A(userName,userpwd) 'qin','qin' union all select 'qin','qin1' union all select 'qin','qin1' select * from A --method one delete from A where userid not in(select min(userid) as userid from A group by username ,userpwd) --method two delete from A where exists (select * from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid --method three delete from a where userid not in(select min(userid) from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid > b.userID) select * from A drop table A

利用临时表方法

删除重复记录,将TABLE_NAME中的不重复记录保存到#TABLE_NAME中

代码如下	复制代码
select distinct * into #table_name from table_name delete from table_name select * into table_name from #table_name drop table #table_name