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

作者:袖梨 2022-06-29

删除数据库中的重复记录(且仅保留一条有效记录)示例-

 代码如下 复制代码

create table A
(
userID int identity(1,1),
userName varchar(20),
userPwd varchar(20),
userEmail varchar(50)
)
insert into A(userName,userpwd) select '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 < b.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

相关文章

精彩推荐