作者：每天洗脸的小媳妇_853 | 来源：互联网 | 2017-05-12 14:55
不管我使用什么数据库都碰到有重复数据的存在了，下面我来给大这详细介绍Oracle 删除表中的重复记录的各种方法与性能分析，有需要了解的朋友可参考。
不管我使用什么数据库都碰到有重复数据的存在了，下面我来给大这详细介绍Oracle 删除表中的重复记录的各种方法与性能分析，有需要了解的朋友可参考。
方法：
| 代码如下 |
|
| XX having count(*)>1,rowid,distinct,temporary table,procedure |
下面语句可以查询出那些数据是重复的：
| 代码如下 |
|
| 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1 |
将上面的>号改为=号就可以查询出没有重复的数据了。
想要删除这些重复的数据，可以使用下面语句进行删除
| 代码如下 |
|
|
delete from 表名 a where 字段1,字段2 in
(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)
|
例
1、查询表中的重复数据
a.重复一个字段
b.重复多个字段
c.重复一整行
创建测试表：
| 代码如下 |
|
|
create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
insert into cfa values (1,'Albert','SCB','2011-11-11');
insert into cfa values (2,'Andy','DB','2011-11-12');
insert into cfa values (3,'Allen','HSBC','2011-11-13');
---------------以下为重复数据----------------------------------------------
insert into cfa values (1,'Alex','ICBC','2011-11-14');
insert into cfa values (1,'Albert','CTBK','2011-11-15');
insert into cfa values (1,'Albert','SCB','2011-11-11');
|
对于a的情况,只有businessid重复
| 代码如下 |
|
|
select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);
|
如果是b的情况，businessid 和name同时存在重复
| 代码如下 |
|
|
select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);
|
对于c的情况，重复一整行
参考b的方法：
| 代码如下 |
|
| select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1); |
2、删除表中的重复数据
a情况，删除表中多余的重复记录，重复记录是根据单个字段（businessid）来判断，只留有rowid最小的记录
也可以只保留rowid不是最小记录，需要把代码中的min改为max这里不再赘述。
| 代码如下 |
|
|
delete from cfa
where businessid in (select businessid
from cfa
group by businessid
having count(businessid) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid
having count(businessid) > 1);
|
或者，使用下面更简单高效的语句
| 代码如下 |
|
|
DELETE FROM cfa t
WHERE t.ROWID >
(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);
|
b情况，删除表中多余的重复记录（多个字段），只留有rowid最小的记录
| 代码如下 |
|
|
delete from cfa
where (businessid,customer) in (select businessid,customer
from cfa
group by businessid,customer
having count(*) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid,customer
having count(*) > 1);
|
或者，使用下面更简单高效的语句
| 代码如下 |
|
|
DELETE FROM cfa t
WHERE t.ROWID > (SELECT MIN(X.ROWID)
FROM cfa X
WHERE X.businessid = t.businessid
and x.customer = t.customer);
|
上面的语句非常简单，就是将查询到的数据删除掉。不过这种删除执行的效率非常低，对于大数据量来说，可能会将吊死。所以我建议先将查询到的重复的数据插入到一个临时表中，然后对进行删除，这样，执行删除的时候就不用再进行一次查询了。如下：
CREATE TABLE 临时表 AS
| 代码如下 |
|
(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)
|
上面这句话就是建立了临时表，并将查询到的数据插入其中。
下面就可以进行这样的删除操作了：
| 代码如下 |
|
delete from 表名 a where 字段1,字段2 in (select 字段1，字段2 from 临时表);
|
这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。
c情况，这种情况就比较简单，使用临时表方法
| 代码如下 |
|
|
create table cfabak as select distinct * from cfa;
truncate table cfa;--如果是生产最好对该表backup
Insert into cfa select * from cfabak;
commit；
|
我们要删除重复数据，只保留最新的一条数据，就可以这样写了：
| 代码如下 |
|
|
delete from 表名 a
where a.rowid !=
(
select max(b.rowid) from 表名 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
)
|
京公网安备 11010802041100号