oracle中如何删除重数据
今天就跟大家聊聊有关oracle中如何删除重数据，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
前期准备
创建测试表
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中如何删除重数据有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。
