SQL Server 批量刪除重複記錄（批量、快速、安全）

關鍵字：ROW_NUMBER（或RANK）  partition BY

利用分組排序，可實現快速、安全、批量的對重複記錄進行刪除，

示例：

建立測試表

CREATE TABLE [dbo].[T_Corse](
	[ID] [INT] IDENTITY(1,1) NOT NULL,--主鍵
	[UserID] [INT] NOT NULL,		  --使用者主鍵
	[CourseCode] [VARCHAR](50) NULL,  --課程程式碼
	[SubjectCode] [VARCHAR](50) NULL, --學段程式碼
	[Creator] [VARCHAR](50) NULL,    
	[CreateTime] [DATETIME] NULL,
 CONSTRAINT [PK_T_Corse] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

新增測試資料（自行新增即可）

分組編號

--按UserID,CourseCode,SubjectCode分組排序
SELECT *,RANK() OVER(PARTITION BY UserID,CourseCode,SubjectCode ORDER BY ID DESC) AS RowNo
FROM dbo.T_Corse;

獲取重複記錄

--重複記錄查詢
SELECT * FROM (
SELECT *,RANK() OVER(PARTITION BY UserID,CourseCode,SubjectCode ORDER BY ID DESC) AS RowNo
FROM dbo.T_Corse
)Ranked WHERE Ranked.RowNo>1;

刪除重複記錄

--刪除重複記錄(保留最新記錄)
DELETE T_Corse 
WHERE id IN(
SELECT ID  FROM(
	SELECT *,RANK() OVER(PARTITION BY UserID,CourseCode,SubjectCode ORDER BY ID DESC) AS RowNo
	FROM dbo.T_Corse
	)Ranked WHERE Ranked.RowNo>1
);