MYSQL 删除重复数据

yangmaolin1234

于 2022-06-10 16:21:34 发布

阅读量122

点赞数

CC 4.0 BY-SA版权

分类专栏： php 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/yangmaolin1234/article/details/125223746

php 专栏收录该内容

9 篇文章

订阅专栏

这篇博客探讨了如何在数据库中查找并删除重复的数据。首先，通过使用`CONCAT`函数结合`GROUP BY`和`HAVING`子句找出姓、名和性别都相同的记录。接着，查询需要删除的数据，特别是那些具有相同姓名和性别的记录，但排除了每个唯一组合的第一个ID。最后，使用`DELETE`语句实际删除这些重复数据。这个过程对于保持数据库的一致性和准确性至关重要。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询姓，名，性别都一样的数据

select concat(xing,ming,sex) as myname,count(1) from `name` GROUP BY xing,ming,sex having count(concat(xing,ming,sex))>1;

查询需要删除的数据

select id,concat(xing,ming,sex),count(id) from  `name`

where

 concat(xing,ming,sex) in (select pming from (select concat(xing,ming,sex) as pming from `name` group by xing,ming,sex having count(concat(xing,ming,sex)) > 1) a)

 and

id  not in 
 (select pid from (select min(id) as pid from `name` group by xing,ming,sex having count(concat(xing,ming,sex)) > 1  ) b)
GROUP BY xing,ming,sex ORDER BY count(id) DESC

删除

DELETE from  `name`

where

 concat(xing,ming,sex) in (select pming from (select concat(xing,ming,sex) as pming from `name` group by xing,ming,sex having count(concat(xing,ming,sex)) > 1) a)

 and

id  not in 
 (select pid from (select min(id) as pid from `name` group by xing,ming,sex having count(concat(xing,ming,sex)) > 1  ) b)