处理多余的重复记录

--1、查找表中多余的重复记录，重复记录是根据单个字段（UserName）来判断
SELECT *
FROM tbUser
WHERE UserName IN
(
    SELECT UserName FROM tbUser
    GROUP BY UserName HAVING COUNT(UserName) > 1
)
AND ID NOT IN (SELECT MIN(ID) FROM tbUser GROUP BY UserName HAVING COUNT(UserName )>1)

--2、删除表中多余的重复记录，重复记录是根据单个字段（UserName）来判断，只留有ID最小的记录
DELETE  
FROM tbUser
WHERE UserName IN
(
    SELECT UserName FROM tbUser
    GROUP BY UserName HAVING COUNT(UserName) > 1
)
AND ID NOT IN (SELECT MIN(ID) FROM tbUser GROUP BY UserName HAVING COUNT(UserName )>1)

--3、查找表中多余的重复记录（多个字段UserNo,UserName）
SELECT a.*
FROM tbUser a,(SELECT UserNo,UserName FROM tbUser GROUP BY UserNo,UserName HAVING COUNT(*) > 1) b
WHERE a.UserNo = b.UserNo AND a.UserName = b.UserName

--4、删除表中多余的重复记录（多个字段UserNo,UserName），只留有ID最小的记录
DELETE a
FROM tbUser a,(SELECT min(ID) AS ID,UserNo,UserName FROM tbUser GROUP BY UserNo,UserName HAVING COUNT(*) > 1) b
WHERE a.UserNo = b.UserNo AND a.UserName = b.UserName AND a.ID <> b.ID

转载于:https://www.cnblogs.com/Frankabc/archive/2012/04/11/%e5%a4%84%e7%90%86%e5%a4%9a%e4%bd%99%e7%9a%84%e9%87%8d%e5%a4%8d%e8%ae%b0%e5%bd%95.html