mysql实现删除数据表重复值，保存最小id

if时光重来

已于 2022-07-01 12:18:09 修改

阅读量470

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

于 2022-05-09 16:02:31 首次发布

本文链接：https://blog.youkuaiyun.com/admin_web/article/details/124668387

mysql 专栏收录该内容

30 篇文章

订阅专栏

这篇博客介绍了如何在数据库中查找并删除重复的数据。通过SQL查询语句，首先选择每个organ_idno下最大account_id，然后删除account_id在这些最大值中的记录，从而实现删除重复值。同时，该方法也适用于其他表，如`user`表。

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

查询重复数据

select max(account_id) as account_id,organ_idno,count(organ_idno) as nums from al_account group by organ_idno having count(organ_idno) > 1;

删除重复值

delete from al_account where account_id in (select t.account_id from (select max(account_id) as account_id from al_account group by organ_idno having count(organ_idno) > 1) as t);

delete from `user` 
where id not in (select id from (select min(id) as id from `user` group by `name`) t)