mysql 查询重复数据 - 孙立亚笔记

mysql 查询重复数据

1、查询表中重复数据,根据一个字段来判断提取

select * from sly_news 
where username in (select username from sly_news group by username having count(username) > 1)

2、查询表中的重复数据,不提取最小id值的数据, 主要用来删除重复值,删除替换最前面的select * 为delete 即可

select * from sly_news 
where username in (select username from sly_news group by username having count(username) > 1 
and id not in (select min(id) from sly_news group by username having count(username) > 1))

3、查询表中重复数据, 根据多个字段判断

select * from sly_news a where (a.username, a.userid) in (
select username, userid from sly_news group by username, userid having count(*) > 1 ) 

添加新评论

电子邮件地址不会被公开,评论内容可能需要管理员审核后显示。