Oracle中如何删除重复数据

ppum4mg8ns

Oracle中如何删除重复数据

     我们可能出现这种情况，某个表原来设计不周全，导致表里面的数据重复，那么如何对重复的数据进行删除呢？

     重复的情况可能有两种，一种是表中某些字段是一样的，或者两条或者多条数据记录是一样的.

1、对部分重复字段的删除：

   首先查询某些有重复字段的纪录：

   SQL:select 字段1，字段2，count(*) from tableName group by 字段1，字段2 having count(*) > 1

将上面的>号修改为= 实现功能为查找没有重复字段的纪录。

   删除这些具有重复字段的数据记录：

   delete from tableName a where 字段1,字段2 in

       (select 字段1,字段2,count(*) from tableName group by 字段1，字段2 having count(*) > 1)

该语句即可以把查询到重复的纪录删除掉。不过该删除纪录速度有点慢，对于大数据容量的数据库来说，可能会将数据库吊死。

     建议：先将符合条件的数据记录(即为要删除的数据库纪录)存取到一个临时数据库表中，然后再进行删除时就不用进行查询了，提高了数据库的删除速度。

    建立临时表：

    SQL:create table tmp_table as

       (select 字段1,字段2 count(*) from tableName group by 字段1，字段2 having count(*)＞１）

　　将重复的数据放到临时表中后，就可以进行删除了。

    delete from tableName where 字段1，字段2 in (select 字段1，字段2 from tmp_table)

    这样先进行临时表再进行删除比用一条SQL语句删除要快的多。

    这个时候，大家可能会说，这样我们不是把所有重复的数据删除掉了吗？而我们想保留重复纪录中最新一条的记录阿！下面讲解以下，如何进行这种操作，保留重复记录中最新的一条记录。

   在Oracle中，有个隐藏了的自动rowid,里面给每条记录唯一的rowid,如果我们想保存最新的一条记录，我们就可以利用这个字段，保存重复数据最大的rowid就可以实现了。

   select a.rowid,字段1,字段2 from tableName a

             where a.rowid!=(select max(b.rowid) from tmpTable b

                    where a.字段1 = b.字段1 and a.字段2 = b.字段2)

这样就可以把所有的重复记录(并非rowid最大)查找出来。里面的sql语句是查找出rowid最大的重复数据记录，而外面的是除去rowid最大外的其他数据记录。当我们要删除这些记录时，可以使用

   SQL:delete from tableName a where a.rowid

       in a.rowid!=(

       select max(b.rowid) from tmpTable b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )

顺便说一下，上面的执行效率是比较低的，此时可以采用将重复的字段放到临时表中

   SQL:create table tmpTable as

       select a.字段1,a.字段2,max(rowid) dataid  from tableName group by a.字段1,a.字段2

   将临时表生成后需要删除其中的数据记录

   delete from tableName a where rowid!=(select dataid from tmptable b where a.字段1 = b.字段1 and a.字段2 = z.字段2 )

  commit;

二、对于数据库表中可能存在的完全重复的记录 比方说有两条或者多条重复一样的记录

  对于表中两行或者多行的数据记录一样的记录，可以采用以下SQL语句将其提取出来

select distinct * from tableName ;

删除其中的记录时可以采用先将数据记录放到一个临时表中，然后再交对将其中的记录删除掉

create table as select distinct * from tableName;

删除其中的冗余记录

delete from tableName;()

insert into tableName (select * from tmpTable);

drop tmpTable;