删除 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;