江湖开发者 | Java粉
网站地图
首页> 数据库> Oracle删除表中重复数据
2017
02-07

Oracle删除表中重复数据

Oracle删除表中重复数据 

 Oracle表中数据的重复,有两种意义的重复记录,一是完全重复的记录,也即所有字段均重复的记录;二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复;删除重复数据,并保留一条即可,发生这种重复的原因是表设计不周产生的,增加唯一索引或者增加主键可彻底解决重复问题。

创建测试表
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');

实验一:模拟staffid单个字段数据重复
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)

结果,共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

删除重复数据,只保留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)

实验二:模拟staffid和staff两个字段数据重复
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



Java江湖     
全部评论:

表情验证码,看不清楚,换一张

随机文章

云标签

公众号

微信

分享:分享我们的知识;专注:专注个人技术的提升;