SQL Server 中删除重复记录

2011 十二月 13

最常用的 T-SQL 语句：

DELETE FROM [dbo].[myTable] WHERE 主键 NOT IN 
    (SELECT MAX(主键) From [dbo].[myTable] GROUP BY 列1, 列2, 列3)

SQL Server 2005 及以上版本，用 CTE：

WITH tmpOrderdTable 
AS 
( 
  SELECT  
    GroupID = ROW_NUMBER() OVER (PARTITION BY 列1, 列2, 列3 ORDER BY 主键) 
  FROM 
    [dbo].[myTable] 
) 
 
DELETE FROM tmpOrderdTable WHERE GroupID > 1

为了提高效率可以先开启单人存取模式，删除完再恢复多人存取模式：

# 开启单人存取模式  
USE [master]  
ALTER DATABASE [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
      
# 开启多人存取模式  
USE [master]  
ALTER DATABASE [myDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE