Mysql——删除数据库中的重复记录

最新推荐文章于 2025-02-12 17:04:22 发布

原创最新推荐文章于 2025-02-12 17:04:22 发布 · 282 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何使用SQL查询来找出数据库表中的重复记录，并提供两种查询方法：一种是查找所有重复记录，另一种是在考虑冗余数据时仅保留一条记录。

查找数据库中的重复记录（多个字段）：
SELECT * FROM wcs_best_fitness_individual WHERE (spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo) IN
(SELECT spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo FROM wcs_best_fitness_individual
GROUP BY spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo HAVING COUNT(*) >1);
查找数据库中多余的重复记录（多个字段，已保留一条数据）：
SELECT * FROM wcs_best_fitness_individual WHERE (spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo) IN
(SELECT spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo FROM wcs_best_fitness_individual
GROUP BY spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo HAVING COUNT(*) >1) AND
id NOT IN (SELECT id FROM wcs_best_fitness_individual GROUP BY
spokeCounts,isSpokeClosure,deflectionAngleOne,deflectionAngleTwo HAVING COUNT(*)>1);