mysql删除重复记录

查找重复记录（多个字段）

select * from tb_userusergroup a where (a.UserId, a.FriendId) IN ( SELECT UserId, FriendId FROM tb_userusergroup GROUP BY UserId, FriendId HAVING COUNT( * ) >1 ) ORDER BY a.UserId, a.FriendId

 

查找多个重复记录（多个字段），不包括Id最小的字段。可以查出来后在phpMyadmin里选中进行删除

select * from tb_userusergroup a
where (a.UserId, a.FriendId) IN ( SELECT UserId, FriendId FROM tb_userusergroup GROUP BY UserId, FriendId HAVING COUNT( * ) >1 )
and a.Id not in ( SELECT min(Id) FROM tb_userusergroup GROUP BY UserId, FriendId HAVING COUNT( * ) >1)

 

注：单个字段的只要简化字段就可以了