数据库中对重复数据行的查询删除操作
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)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
