SQL2005中如何删除某字段重复的数据
依下图Students表内容为例,Id_P列不同,Id_P类型为int,自增字段
一、查询重复的数据
（Name,Address,City列值重复）
SELECT Name, Address, City, COUNT(*) AS CountStudents
FROM Students
GROUP BY Name, Address, City
HAVING (COUNT(*) > 1)
查询结果如下图：
通过查询结果可以看出Name,Address,City列值重复的有哪些及重复次数
SELECT MAX(Id_P) AS MAXId_P
FROM Students
GROUP BY Name, Address, City
查询结果如下图：
依Name, Address, City分组查询Id_P最大值，这样查询的结果就不包含重复的Id_P值
SELECT *
FROM Students
WHERE (Id_P IN
(SELECT MAX(Id_P) AS MAXId_P
FROM Students AS Students_1
GROUP BY Name, Address, City))
查询结果如下图：
查询结果没有重复的数据，可以把MAX（Id_P）改成MIN（Id_P）
DELETE FROM Students
WHERE (Id_P NOT IN
(SELECT MAX(Id_P) AS MAXId_P
FROM Students AS Students_1
GROUP BY Name, Address, City))
删除重复的数据，注意Id_P后面"NOT IN"
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请
点击举报。
