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 bsdf21 rows selected1.查出重复数据方法一SELECT *FROM salary aWHERE ((SELECT COUNT(*) FROM salary WHERE staffid = a.staf免费云主机域名fid) > 1)ORDER BY staffid方法二select * from salarywhere staffid in(select staffid from salary group by staffid having count(staffid) > 1)删除重复数据，只保留1条，其余全部删除方法一，通过rowid删除delete from salarywhere 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 awhere (a.staffid,a.staff) in (select staffid,staff from salary group by staffid,staff having count(*) > 1)方法二SELECT *FROM salary aWHERE ((SELECT COUNT(*) FROM salary WHERE staffid = a.staffid and staff=a.staff) > 1)ORDER BY staffid结果，共15条STAFFIDSTAFF1
a1
a1
a2
s2
s2
s3
ert3
ert3
ert4
d4
d4
d5
b5
b5
b2.删除重复数据，只保留1条，其余全部删除delete from vitae awhere (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;结果STAFFIDSTAFF1
oookkk1
a2
s3
ert4
d5
b10
aaaa20
sass30
erwt40
dsd50
bsdf看完上述内容，你们对oracle中如何删除重数据有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注百云行业资讯频道，感谢大家的支持。

相关推荐: B站十年沉浮录：盈利困境阴云不散

6月份，B站在上海举办了自己的十周年庆典，逐渐成熟的B站影响力越来越大。B站十周年的微博话题下，曝光量48小时就达到了2.5亿的高峰，讨论量高达22.2万。《三体》动画化的消息更是点燃了全网，这个消息牢牢占据了各大社交平台热搜榜首。 十岁的B站，风华正茂。极高…