我们分享了二个实现，来告诉你如何把数据表中重复记录的记录给删除只保留一条，这种方法风险性比较大，另一种方法利用临时表，这种方法相对上种要好些。
删除中的重复记录（且仅保留一条有效记录）示例-
 代码如下 复制代码
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