初探oracle删除重复记录，只保留rowid最小的记录
如题，初探oracle删除重复记录，只保留rowid最小的记录（rowid可以反映数据插入到数据库中的顺序）
一、删除重复记录可以使用多种方法，如下只是介绍了两种方法(exist和in两种)。
1.首先创建一个测试表。
create table my_users(
id number,
username varchar2(20),
sal number
)
2.插入测试数据
begin
for i in 1..10 loop
insert into my_users values(i,'carl_zhang',i+10);
end loop;
end; begin
for i in 1..10 loop
insert into my_users values(i,'carl_zhang',i+20);
end loop;
end; insert into my_users values(100,'carl',20.3); commit;
3.查看重复记录
select rowid,rownum,a.* from my_users a
where 1=1
and exists(
select 'exist' from my_users b
where 1=1
and a.id=b.id
and a.username=b.username
having count(*)>1
)
order by rowid
4.查看重复数据中，rowid最大的记录（rowid可以反映数据插入到数据库中的顺序）
select rowid,rownum,a.* from my_users a
where 1=1
and exists(
select 'exist' from my_users b
where 1=1
and a.id=b.id
and a.username=b.username
-- having count(*)>1
having count(*)>1 and a.rowid=max(b.rowid)
)
order by rowid
5.删除重复数据，保留rowid最小的记录
delete from my_users a
where 1=1
and exists(
select 'exist' from my_users b
where 1=1
and a.id=b.id
and a.username=b.username
-- having count(*)>1
having count(*)>1 and a.rowid=max(b.rowid)
)
二、以上方法是通过exist实现，相比in、not in更加的快速。
1.如下，查看重复记录。
select rowid,rownum,a.* from my_users a
where 1=1
and (a.id,a.username) in(
select b.id,b.username from my_users b
where 1=1
having count(*)>1
group by b.id,b.username
)
order by rowid
2.查看重复数据中，rowid最大的记录
select rowid,rownum,a.* from my_users a
where 1=1
and (a.id,a.username,rowid) in(
select b.id,b.username,max(rowid) from my_users b
where 1=1
having count(*)>1
group by b.id,b.username
)
order by rowid
3.删除重复数据，保留rowid最小的记录
delete from my_users a
where 1=1
and (a.id,a.username,rowid) in(
select b.id,b.username,max(rowid) from my_users b
where 1=1
having count(*)>1
group by b.id,b.username
)