Question

如何删除 SQL Server 表中的重复行

Answer 1

假设表名为：ta
其中 id 为主键
其中：key_name 就是你需要不发生重复的字段
删除重复项
------------------------------------------------------------------------------------
delete ta where id not in
( select max(id) from group by key_name )
------------------------------------------------------------------------------------
要显示不重复项的数据
select * from ta where id in
( select max(id) from group by key_name )

Answer 2

删除表中重复的行，如：TUser表中有3条重复的数据，现在希望删掉前2条，保留第3条
WITH [CTE DUPLICATE] as
(SELECT RN=ROW_NUMBER() OVER (ORDER BY name)
FROM TUser where Name='Tom'
)
delete from [CTE DUPLICATE] where RN<3
执行该sql语句后，数据库中记录有5条变为3条