mysql查询重复数据

最新推荐文章于 2024-08-22 09:35:23 发布

21-夜一

最新推荐文章于 2024-08-22 09:35:23 发布

阅读量268

点赞数

分类专栏： mysql查询，优化

原文链接：https://www.cnblogs.com/LDDXFS/p/9867928.html

版权

mysql查询，优化专栏收录该内容

7 篇文章 0 订阅

订阅专栏

表数据在这里插入图片描述

1 查询people_no重复的记录

select * from people 
where people_no in 
(select people_no from people 
group by people_no 
having count(people_no) > 1);

在这里插入图片描述
2 查询people_no重复的记录，排除最小id，如果删除改为delete from

select * from people
where people_no in 
(select people_no from people 
group by people_no 
having count(people_no) > 1)
and id not in 
(select min(id) from people 
group by people_no 
having count(people_no)>1);

在这里插入图片描述
3 查询people_no people_name重复的记录

select * from people a
where (a.people_no,a.people_name) in 
(select people_no,people_name from people 
GROUP BY people_no,people_name
HAVING count(*)>1);

在这里插入图片描述
4 查询people_no people_name重复的记录，排除最小id

select * from people a
where (a.people_no,a.people_name) in 
(select people_no,people_name from people 
GROUP BY people_no,people_name 
HAVING count(*)>1) and
a.id not in    
(select min(id) from people 
GROUP BY people_no,people_name
 HAVING count(*)>1);