标签搜索
搜索到
1
篇与
Mysql
的结果
-
Mysql数据库删除重复记录只保留一条 查出重复的typeSELECT type FROM table GROUP BY type HAVING count(type) > 1;查出重复type数据中最小的idSELECT min(id) FROM table GROUP BY type HAVING count(type) > 1;查出重复的type数据中非最小的id（要删的数据）SELECT id FROM table WHERE type in( SELECT type FROM table GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1);在Mysql中是不能删除查询出来的记录，而是要通过一张临时表来解决SELECT id from ( SELECT id FROM table WHERE type in( SELECT type FROM table GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1) ) as t;删除type重复的数据(只保留一条，保留最小id的)DELETE FROM table WHERE id IN ( SELECT id from ( SELECT id FROM table WHERE type in( SELECT type FROM table GROUP BY type HAVING count(type) > 1) AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1) ) as t );
