SQL Server 批量删除重复记录(批量、快速、安全)

时间:2022-09-04 06:24:53

关键字: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;
SQL Server 批量删除重复记录(批量、快速、安全)

获取重复记录

--重复记录查询
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;

SQL Server 批量删除重复记录(批量、快速、安全)

删除重复记录

--删除重复记录(保留最新记录)
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
);