首页 > 代码库 > 数据库中对重复数据行的查询删除操作

数据库中对重复数据行的查询删除操作

oracle中对重复数据的查询和删除操作

--1.查询表中username=‘lingjie’的重复记录
select userid,username from nmb where username in(
select username from nmb group by username having count(username)>1)

--2.删除表中username 重复的数据,只保留rowid最小的一条
delete from nmb where username in
(select username from nmb group by username having count(username)>1)
and rowid not in (select min(rowid) from nmb group by username having count(username)>1)

--3.查询表中多余重复的记录(多个字段)
select * from nmb n
where(n.username,n.pwd) in
(select username,pwd from nmb group by username,pwd having count(*)>1)

--4.删除表中重复的记录(多个字段),只保留rowid最小的一条
delete from nmb n
where (n.username,n.pwd) in
(select username,pwd from nmb group by username,pwd having count(*)>1)
and rowid not in
(select min(rowid) from nmb group by username,pwd having count(*)>1)

--5.查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from nmb n
where(n.username,n.pwd) in
(select username,pwd from nmb group by username,pwd having count(*)>1)
and rowid not in
(select min(rowid) from nmb group by username,pwd having count(*)>1)