sql-server-2008-r2 - 删除SQL Server中的重复记录

Question

我写了一个查询，从表中删除重复的记录

;WITH a as
(
SELECT Firstname,ROW_NUMBER() OVER(PARTITION by Firstname, empID ORDER BY Firstname) 
AS duplicateRecCount
FROM dbo.tblEmployee
)
--Now Delete Duplicate Records
DELETE FROM tblEmployee
WHERE duplicateRecCount > 1 

但是我不知道我哪里出了错


无效的列名 duplicateRecCount


有人能帮我吗？

Answer 1

您需要在delete语句中引用CTE。

WITH a as
(
SELECT Firstname,ROW_NUMBER() OVER(PARTITION by Firstname, empID ORDER BY Firstname) 
AS duplicateRecCount
FROM dbo.tblEmployee
)
--Now Delete Duplicate Records
DELETE FROM a
WHERE duplicateRecCount > 1