1 引言
在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来读诸多不便,那么怎么删除这些重复没有用的数据呢?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452 :不能创建唯一索引,发现重复记录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
2 处理过程
重复的数据可能有这样两种情况:第一种是表中只有某些字段一样,第二种是两行记录完全一样。删除重复记录后的结果也分为2种,第一种是重复的记录全部删除,第二种是重复的记录中只保留最新的一条记录,一般业务中第二种的情况较多。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
删除重复记录的方法原理
(1)在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
(2)在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中那些具有最大rowid的就可以了,其余全部删除。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
删除部分字段重复数据
2. 重复记录全部删除
想要删除部分字段重复的数据,可以使用下面语句进行删除,下面的语句是删除表中字段1和字段2重复的数据:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM 表名 a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE (字段1, 字段2)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
IN (SELECT 字段1,字段2 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM 表名文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
GROUP BY 字段1,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
字段2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
HAVING COUNT(1) > 1)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
上面的语句非常简单,就是将查询到的数据删除掉。不过这种删除执行的效率非常低,对于大数据量来说,可能会将数据库吊死。所以建议先将查询到的重复的数据插入到一个临时表中,然后进行删除,这样,执行删除的时候就不用再进行一次查询了。如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
CREATE TABLE 临时表 AS(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
上面这句话就是建立了临时表,并将查询到的数据插入其中。下面就可以进行这样的删除操作了:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
delete from 表名 a where 字段1,字段2 in (select 字段1,字段2 from 临时表);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
例子:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM tmp_lhr t文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE (t.accesscode, t.lastserviceordercode, t.serviceinstancecode) IN文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
(SELECT a.accesscode, ,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM tmp_lhr a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
GROUP BY a.accesscode,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
HAVING COUNT(1) > 1);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
.2 保留最新的一条记录
假如想保留重复数据中最新的一条记录啊!那怎么办呢?在oracle中,有个隐藏了自动rowid,里面给每条记录一个唯一的rowid,我们如果想保留最新的一条记录,我们就可以利用这个字段,保留重复数据中rowid最大的一条记录就可以了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
一、 如何查找重复记录?
SELECT *文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM TABLE_NAME A文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE ROWID NOT IN (SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM TABLE_NAME D文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE A.COL1 = D.COL1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
AND A.COL2 = D.COL2);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
二、 如何删除重复记录?
1、 方法1
DELETE FROM TABLE_NAME文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE ROWID NOT IN (SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM TABLE_NAME D文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
group by d.col1,d.col2);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
这种方法最简单!!!文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
2、 方法2
DELETE FROM TABLE_NAME A文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE ROWID NOT IN (SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM TABLE_NAME D文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE A.COL1 = D.COL1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
AND A.COL2 = D.COL2);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
3、 方法3 临时表
由此,我们要删除重复数据,只保留最新的一条数据,就可以这样写了:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
create table 临时表 as select a.字段1,a.字段2,MAX() dataid from 正式表 a GROUP BY a.字段1,a.字段2;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM 正式表 a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
where a.rowid NOT IN (SELECT b.dataid文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM 临时表 b文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE a.字段1 = b.字段1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
and a.字段2 = b.字段2);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
commit;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
例子:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM tmp_lhr t文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE not in (SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM tmp_lhr a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
GROUP BY a.accesscode,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM tmp_lhr t文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE !=文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
(SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM tmp_lhr a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE a.accesscode = t.accesscode文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
AND = t.lastserviceordercode文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
AND = t.serviceinstancecode);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
.3 删除以某个字段为准的记录
----任意保留一条记录文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM ods_entity_info_full_lhr_01 T文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE T.ROWID NOT IN (SELECT MAX()文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM ods_entity_info_full_lhr_01 A文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
GROUP BY entity_code,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
entity_type);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
---保留 entity_id 最大的一条记录文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
DELETE FROM ods_entity_info_full_lhr_01 a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE a.rowid NOT IN文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
(SELECT文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM ods_entity_info_full_lhr_01 t文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE (t.entity_code, t.entity_type, t.entity_id) IN文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
(SELECT entity_code,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
entity_type,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
MAX(entity_id)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM ods_entity_info_full_lhr_01文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
GROUP BY entity_code,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
entity_type));文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
删除完全重复记录
对于表中两行记录完全一样的情况,可以用下面三种方式获取到去掉重复数据后的记录:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
- selectdistinct * from 表名;
- select * from表名 group by 列名1,列名2,... having count(*)>1
- select * from 表名 a where rowid<(select max(rowid) from 表名 b where a.列名1=b.列名2 and ...)
.1 方法1
DELETE FROM tmp_lhr t文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE not in (SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM tmp_lhr a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
GROUP BY a.accesscode,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
.2 方法2
可以将查询的记录放到临时表中,然后再将原来的表记录删除,最后将临时表的数据导回原来的表中。如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
CREATE TABLE 临时表 AS (select distinct * from 表名);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
truncate table 正式表;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
insert into 正式表 (select * from 临时表);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
drop table 临时表;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
.3 方法3
DELETE FROM xr_maintainsite E文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE E.ROWID > (SELECT MIN()文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
FROM xr_maintainsite X文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
WHERE X.Maintainid = E.Maintainid文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
AND x.siteid = e.siteid);--这里被更新表中所有字段都需要写全文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
采用row_number分析函数取出重复的记录然后删除序号大于1的记录
给出一个例子:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
delete from aa where rowid in(select rid from(select rowid rid,row_number() over (partition by name order by id) as seq from aa) where seq>1);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
3 测试案例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> CREATE TABLE T_ROWS_LHR_20160809 AS SELECT * FROM SCOTT.EMP;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
Table created.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
14 rows created.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> COMMIT;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
Commit complete.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
28 rows created.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> COMMIT;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
Commit complete.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
COUNT(1)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
----------文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
56文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> DELETE FROM T_ROWS_LHR_20160809文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
2 WHERE ROWID NOT IN (SELECT MAX(ROWID)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
3 FROM T_ROWS_LHR_20160809 D文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
4 group by D.EMPNO,D.ENAME,D.JOB,D.MGR,D.DEPTNO);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
42 rows deleted.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
COUNT(1)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
----------文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
14文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
SYS@raclhr1> COMMIT;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
Commit complete.文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
4 经验总结
重复数据删除技术可以提供更大的备份容量,实现更长时间的数据保留,还能实现备份数据的持续验证,提高数据恢复服务水平,方便实现数据容灾等。Oracle数据库重复数据删除技术有如下优势:更大的备份容量、数据能得到持续验证、有更高的数据恢复服务水平、方便实现备份数据的容灾。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html
通过摸索,相信你能发现更多更高效删除Oracle重复数据的方法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/21380.html