MySQL查找删除重复数据只保留一条
1132
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
);