oracle中如何删除重数据

共计 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 行业资讯频道，感谢大家的支持。