删除数据表中的重复数据

以下内容整理自：

• http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows
• http://www.w3resource.com/sql/delete-statement/delete-with-subqueries.php
  

示例表结构如下：

MyTable
-----------
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

解决方法：

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

如果是复合主键的话，需要把整个子查询放在EXISTS子句中，EXISTS用法如下：

DELETE FROM agent1 da  
 WHERE EXISTS(  
  SELECT * FROM  customer cu  
   WHERE grade=3  
     AND da.agent_code<>cu.agent_code);