mysql实现查重只留一个的方法

117次阅读

共计 2129 个字符，预计需要花费 6 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章将为大家详细讲解有关 mysql 实现查重只留一个的方法，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

mysql 实现查重只留一个的方法：首先通过“select * from”查找表中多余的重复记录；然后通过“delete from”删除重复数据，并只保留一个数据即可。

mysql 删除重复数据只保留一条记录

删除重复数据保留 name 中 id 最小的记录

delete from order_info where id not in (select id from (select min(id) as id from order_info group by order_number) as b);

delete from table where id not in (select min(id) from table group by name having count(name) 1) and id in (select id group by name having count(name) 1)

(注意：HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。)

扩展：

SQL：删除重复数据，只保留一条用 SQL 语句, 删除掉重复项只保留一条在几千条记录里, 存在着些相同的记录, 如何能用 SQL 语句, 删除掉重复的呢

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有 rowid 最小的记录

delete from people where peopleName in (select peopleName from people group by peopleName having count(peopleName) 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName) 1)

3、查找表中多余的重复记录（多个字段）

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)

4、删除表中多余的重复记录（多个字段），只留有 rowid 最小的记录

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)

5、查找表中多余的重复记录（多个字段），不包含 rowid 最小的记录

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)

6. 消除一个字段的左边的第一位：

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like 村 %

7. 消除一个字段的右边的第一位：

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like % 村

8. 假删除表中多余的重复记录（多个字段），不包含 rowid 最小的记录

update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) 1) and seq in（select seq from vitae group by peopleId,seq having count(*) 1）and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*) 1)

关于 mysql 实现查重只留一个的方法就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！

正文完