==============================
?Copyright 蕃薯耀?2023-01-31文章來源：http://www.zghlxwxcb.cn/news/detail-595287.html
蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js領域博主文章來源地址http://www.zghlxwxcb.cn/news/detail-595287.html
一、Mysql 刪除重復數(shù)據(jù)，保留一條有效數(shù)據(jù)
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
)
;原理：
- 根據(jù)字段對數(shù)據(jù)進行分組，查詢出所有分組的最小ID（即要保留的不重復數(shù)據(jù)）
- 將查詢出來的數(shù)據(jù)（所有不重復的數(shù)據(jù)）存放到臨時表中
- 從原來的表中刪除ID不在臨時表中的重復數(shù)據(jù)
當表的數(shù)據(jù)量達到百萬級以上時，刪除數(shù)據(jù)很慢，建議這樣處理：
- 先把min(id)存到臨時表（SZ_Building_id）：
create table SZ_Building_id
SELECT MIN(id) AS id FROM SZ_Building GROUP BY BLDG_NO
;- 給臨時表的id設置成主鍵或者創(chuàng)建索引（加快后面的查詢速度）
alter table add index ind_xxName(字段名：如ID);- 刪除id不在臨時表的數(shù)據(jù)。
delete from ?SZ_Building where id not in(
select id from SZ_Building_id
)
;二、Mysql 刪除重復數(shù)據(jù)（多個字段分組）
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
)
;三、Mysql 查詢出可以刪除的重復數(shù)據(jù)
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領域博主
到了這里，關于Mysql 刪除重復數(shù)據(jù)保留一條有效數(shù)據(jù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！
