關(guān)鍵字：ROW_NUMBER（或RANK）  partition BY

利用分組排序，可實(shí)現(xiàn)快速、安全、批量的對重復(fù)記錄進(jìn)行刪除，

示例：

創(chuàng)建測試表

CREATE TABLE [dbo].[T_Corse](	[ID] [INT] IDENTITY(1,1) NOT NULL,--主鍵	[UserID] [INT] NOT NULL,		  --用戶主鍵	[CourseCode] [VARCHAR](50) NULL,  --課程代碼	[SubjectCode] [VARCHAR](50) NULL, --學(xué)段代碼	[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]
添加測試數(shù)據(jù)（自行添加即可）
分組編號(hào)
--按UserID,CourseCode,SubjectCode分組排序SELECT *,RANK() OVER(PARTITION BY UserID,CourseCode,SubjectCode ORDER BY ID DESC) AS RowNoFROM dbo.T_Corse;
獲取重復(fù)記錄
--重復(fù)記錄查詢SELECT * FROM (SELECT *,RANK() OVER(PARTITION BY UserID,CourseCode,SubjectCode ORDER BY ID DESC) AS RowNoFROM dbo.T_Corse)Ranked WHERE Ranked.RowNo>1;
刪除重復(fù)記錄
--刪除重復(fù)記錄(保留最新記錄)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);