删除数据库中重复的记录
2012-01-04 12:30 诺言 阅读(214) 评论(1) 编辑 收藏 举报查找所有唯一的记录，删除其他记录。
最常用的 T-SQL 语句：
DELETE FROM [dbo].[myTable] WHERE 主键 NOT IN
(SELECT MAX(主键) From [dbo].[myTable] GROUP BY 列1, 列2, 列3)
(SELECT MAX(主键) From [dbo].[myTable] GROUP BY 列1, 列2, 列3)
列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]
)
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]
USE [master]
ALTER DATABASE [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
# 开启多人存取模式
USE [master]
ALTER DATABASE [myDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
原文：http://www.loveyuki.com/blog/sql-serve-delete-duplicate-records
