============================== ©Copyright 蕃薯耀 2023-01-31 蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主 一、Mysql 删除重复数据,保留一条有效数据 DELET
==============================
©Copyright 蕃薯耀 2023-01-31
蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主
DELETE FROM SZ_Building WHERE id NOT IN ( SELECT t.min_id FROM ( SELECT MIN(id) AS min_id FROM SZ_Building GROUP BY BLDG_NO ) t);
原理:
当表的数据量达到百万级以上时,删除数据很慢,建议这样处理:
create table SZ_Building_idSELECT MIN(id) AS id FROM SZ_Building GROUP BY BLDG_NO;
alter table add index ind_xxName(字段名:如ID);
delete from SZ_Building where id not in( select id from SZ_Building_id);
DELETE FROM SZ_Water_Level WHERE id NOT IN ( SELECT t.min_id FROM ( SELECT MIN(id) AS min_id FROM SZ_Water_Level GROUP BY CZBM,SJ,SW ) t);
SELECT * FROM SZ_Building WHERE id NOT IN ( SELECT t.min_id FROM ( SELECT MIN(id) AS min_id FROM SZ_Building GROUP BY BLDG_NO ) t);
或
SELECT * FROM SZ_Building WHERE BLDG_NO IN (SELECT BLDG_NO FROM SZ_Building GROUP BY BLDG_NO HAVING COUNT(1)>1) AND id NOT IN (SELECT MIN(id) FROM SZ_Building GROUP BY BLDG_NO HAVING COUNT(1)>1);
==============================
©Copyright 蕃薯耀 2023-01-31
蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主
来源地址:https://blog.csdn.net/w995223851/article/details/128816753
--结束END--
本文标题: Mysql 删除重复数据保留一条有效数据
本文链接: https://www.lsjlt.com/news/395133.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0