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条 STAFFID STAFF 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; 结果 STAFFID STAFF 1      oookkk 1      a 2      s 3      ert 4      d 5      b 10    aaaa 20    sass 30    erwt 40    dsd 50    bsdf