数据库进阶：删除完全重复和部分关键字段重复的记录

重复记录分为两种第一种是完全重复的记录也就是所有字段均重复的记录第二种是部分关键字段重复的记录例如Name字段重复而其它字段不一定重复或都重复

第一种重复很容易解决不同数据库环境下方法相似

Mysql

create table tmp select distinct * from tableName;

drop table tableName;

create table tableName select * from tmp;

drop table tmp;

SQL Server

select distinct * into #Tmp from tableName;

drop table tableName;

select * into tableName from #Tmp;

drop table #Tmp;

Oracle

create table tmp as select distinct * from tableName;

drop table tableName;

create table tableName as select * from tmp;

drop table tmp;

发生这种重复的原因是由于表设计不周而产生的增加唯一索引列就可以解决此问题

此类重复问题通常要求保留重复记录中的第一条记录操作方法如下 假设有重复的字段为NameAddress要求得到这两个字段唯一的结果集

Mysql

alter table tableName add autoID int auto_increment not null;

create table tmp select min(autoID) as autoID from tableName group by NameAddress;

create table tmp select tableName* from tableNametmp where tableNameautoID = tmpautoID;

drop table tableName;

rename table tmp to tableName;

SQL Server

select identity(int) as autoID * into #Tmp from tableName;

select min(autoID) as autoID into #Tmp from #Tmp group by NameAddress;

drop table tableName;

select * into tableName from #Tmp where autoID in(select autoID from #Tmp);

drop table #Tmp;

drop table #Tmp;

Oracle

DELETE FROM tableName t WHERE tROWID > (SELECT MIN(tROWID) FROM tableName t WHERE tName = tName and tAddress = tAddress);

说明

MySQL和SQL Server中最后一个select得到了NameAddress不重复的结果集（多了一个autoID字段在大家实际写时可以写在select子句中省去此列）

因为MySQL和SQL Server没有提供rowid机制所以需要通过一个autoID列来实现行的唯一性而利用Oracle的rowid处理就方便多了而且使用ROWID是最高效的删除重复记录方法