【学习笔记】SqlServer删除重复数据保留一条（好用）

方法一、

1、查询出重复的数据，Id只保留其中一个

SELECT MIN(Id) Id,Name FROM dbo.City 
GROUP BY Name
HAVING COUNT(1) >  1

2、然后使用删除时Join上面的表

DELETE C FROM City C 
JOIN (
    SELECT MIN(Id) Id,Name FROM dbo.City
    GROUP BY Name
    HAVING COUNT(1) >  1
) TMP ON C.Name = TMP.Name AND C.Id <> TMP.Id

方法二、

1.查询单列重复：

select * from test
where name in (select name from test group by name having count
(name) > 1)

2.查询多列重复

SELECT a.* FROM test a,(
SELECT name,code
FROM test
GROUP BY name,code
HAVING COUNT(1)>1

) AS b
WHERE a.name=b.name AND a.code=b.code