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)