删除 SQL Server 中的重复记录? [英] Delete duplicate records in SQL Server?

问题描述

考虑名为 EmployeeName 表 Employee 的列.目标是根据 EmployeeName 字段删除重复记录.

Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field.

EmployeeName
------------
Anand
Anand
Anil
Dipak
Anil
Dipak
Dipak
Anil

使用一个查询，我想删除重复的记录.

Using one query, I want to delete the records which are repeated.

如何在 SQL Server 中使用 TSQL 来实现这一点?

How can this be done with TSQL in SQL Server?

推荐答案

您可以使用窗口函数来做到这一点.它将按 empId 对欺骗进行排序，并删除除第一个之外的所有内容.

You can do this with window functions. It will order the dupes by empId, and delete all but the first one.

delete x from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

将其作为选择运行以查看将删除的内容:

Run it as a select to see what would be deleted:

select *
from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

这篇关于删除 SQL Server 中的重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！