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 RowNoFROM dbo.T_Corse;
获取重复记录
--重复记录查询SELECT * FROM (SELECT *,RANK() OVER(PARTITION BY UserID,CourseCode,SubjectCode ORDER BY ID DESC) AS RowNoFROM 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);