这是我表中的列:
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
这篇关于如何使用 OVER 子句删除 SQL Server 中的重复行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!