sql - 从表中删除具有相同 ID 的重复行

Question

我有一个 Emp 表，里面有这样的记录

Id     Name
1      A
2      B
3      C
1      A
1      A
2      B
3      C

现在我想从表中删除重复的行我正在使用此查询来选择或计算重复记录的数量

SELECT NameCol, COUNT(*) as TotalCount FROM TestTable 
GROUP BY NameCol HAVING COUNT(*) > 1 
ORDER BY COUNT(*) DESC

我应该写什么查询来删除表中的重复行。

如果我编写此查询以删除重复记录，则会给出 (0) row Affected 结果。

`DELETE FROM TestTable 
    WHERE ID NOT IN ( SELECT MAX(ID) FROM 
                                     TestTable 
                                     GROUP BY NameCol
                    )`

Answer 1

对于 sqlserver 2005+

测试数据:

declare @t table(Id int, Name char(1))
insert @t values
(1,'A'),(2,'B'),(3,'C'),(1,'A'),(1,'A'),(2,'B'),(3,'C')

删除语句(将@t 替换为您的 Emp 表)

;with a as
(
select row_number() over (partition by id, name order by id) rn
from @t
)
delete from a where rn > 1

select * from @t