删除 SQL Server 中的重复记录 [英] Deleting duplicate record in SQL Server

问题描述

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

I have written a query to remove duplicate records from a table

;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 

但我不知道我哪里出错了

But I don't know where I went wrong it is saying

无效的列名duplicateRecCount

有人可以帮我吗?

推荐答案

需要在delete语句中引用CTE...

You need to reference the CTE in the delete statement...

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

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