共计 2180 个字符，预计需要花费 6 分钟才能阅读完成。
今天就跟大家聊聊有关 oracle 中如何删除重数据，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
前期准备
创建测试表
create table salary(
staffid int,
staff varchar(15)
);
模拟重复数据
insert into salary values(1, a
insert into salary values(2, s
insert into salary values(3, ert
insert into salary values(4, d
insert into salary values(5, b
insert into salary values(1, a
insert into salary values(2, s
insert into salary values(3, ert
insert into salary values(4, d
insert into salary values(5, b
insert into salary values(1, a
insert into salary values(2, s
insert into salary values(3, ert
insert into salary values(4, d
insert into salary values(5, b
insert into salary values(10, aaaa
insert into salary values(20, sass
insert into salary values(30, erwt
insert into salary values(40, dsd
insert into salary values(50, bsdf
insert into salary values(1, oookkk
实验一：模拟单个字段数据重复
select * from salary;
STAFFID STAFF
————— —————
1 oookkk
1 a
2 s
3 ert
4 d
5 b
1 a
2 s
3 ert
4 d
5 b
1 a
2 s
3 ert
4 d
5 b
10 aaaa
20 sass
30 erwt
40 dsd
50 bsdf
21 rows selected
1. 查出重复数据
方法一
SELECT *
FROM salary a
WHERE ((SELECT COUNT(*)
FROM salary
WHERE staffid = a.staffid) 1)
ORDER BY staffid
方法二
select *
from salary
where staffid in
(select staffid from salary group by staffid having count(staffid) 1)
删除重复数据，只保留 1 条，其余全部删除
方法一，通过 rowid 删除
delete from salary
where staffid in (select staffid from salary group by staffid having count(staffid) 1)
and rowid not in (select min(rowid) from salary group by staffid having count(staffid) 1)
实验二：模拟两个个字段数据重复
1. 查询重复记录
方法一
select * from salary a
where (a.staffid,a.staff) in (select staffid,staff from salary group by staffid,staff having count(*) 1)
方法二
SELECT *
FROM salary a
WHERE ((SELECT COUNT(*)
FROM salary
WHERE staffid = a.staffid and staff=a.staff) 1)
ORDER BY staffid
结果，共 15 条
STAFFIDSTAFF
1
a
1
a
1
a
2
s
2
s
2
s
3
ert
3
ert
3
ert
4
d
4
d
4
d
5
b
5
b
5
b
2. 删除重复数据，只保留 1 条，其余全部删除
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)
3. 查看删除后结果
select * from salary;
结果
STAFFIDSTAFF
1
oookkk
1
a
2
s
3
ert
4
d
5
b
10
aaaa
20
sass
30
erwt
40
dsd
50
bsdf
看完上述内容，你们对 oracle 中如何删除重数据有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注丸趣 TV 行业资讯频道，感谢大家的支持。
