MySQL查找删除重复数据只保留一条
1345
按ID删除，保留最小的ID，如果表数量大，分批执行。
SELECT id,title FROM `article` a where a.id>(select min(id) from article b where a.title=b.title);
DELETE from tab where
-- 删除所有的重复时间 Begin --
`name` in (
SELECT * from (SELECT `name`FROM TAB GROUP BY `name` HAVING COUNT(1) >1) tmp2
)
-- 删除所有的重复时间 END --
-- 但一些特定ID的记录不进行删除 Begin --
AND
id NOT in(
select id from (
SELECT `name`,id FROM TAB GROUP BY `name` HAVING COUNT(1) >1
) tmp1
)
-- 但一些特定ID的记录不进行删除
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
);
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
);
