如何使用 OVER 子句删除 SQL Server 中的重复行?

问题描述

这是我表中的列:

Id
EmployeeId
IncidentRecordedById
DateOfIncident
Comments
TypeId
Description
IsAttenIncident

我想删除 EmployeeId、DateOfIncident、TypeId 和 Description 相同的重复行 - 只是为了澄清 - 我确实想保留其中之一.我想我应该将 OVER 子句与 PARTITION 一起使用，但我不确定.

I would like to delete duplicate rows where EmployeeId, DateOfIncident, TypeId and Description are the same - just to clarify - I do want to keep one of them. I think I should be using the OVER clause with PARTITION, but I am not sure.

谢谢

推荐答案

如果您想保留一行重复组，您可以使用 ROW_NUMBER.在此示例中，我保留具有最低 Id 的行:

If you want to keep one row of the duplicate-groups you can use ROW_NUMBER. In this example i keep the row with the lowest Id:

WITH CTE AS 
(
    SELECT rn = ROW_NUMBER() 
                OVER( 
                  PARTITION BY employeeid, dateofincident, typeid, description 
                  ORDER BY Id ASC), * 
    FROM dbo.TableName
) 
DELETE FROM cte 
WHERE  rn > 1