mysql删除表中重复数据

---

SQL语句

1.格式

代码如下（当前示例为软删除，且以自增主键字段进行取舍，保留最新的一条数据）：

# select * from `表名`
 UPDATE `表名` SET `软删除字段` = `软删除值`
  WHERE (`需要根据分组来进行筛选的字段`) IN
   (SELECT * FROM
    (SELECT `需要根据分组来进行筛选的字段` FROM `表名`
     GROUP BY  `需要根据分组来进行筛选的字段`
      HAVING COUNT(*) > 1) AS a)
  AND `主键字段` NOT IN
  (SELECT * FROM
    (SELECT MAX(`主键字段`) FROM `表名`
     GROUP BY `需要根据分组来进行筛选的字段`
      HAVING COUNT(*) > 1) AS b)

2.示例

# select id,title,city_id,city_column_id from `tree_news`
 UPDATE `tree_news` SET delete_time = 1675220994
 WHERE (title, city_id, `city_column_id`) IN
  (SELECT  * FROM
   (SELECT title, city_id, `city_column_id` FROM `tree_news`
    GROUP BY title, city_id, `city_column_id`
    HAVING COUNT(*) > 1) AS a)
 AND id NOT IN
 (SELECT * FROM (SELECT MAX(id) FROM `tree_news`
    GROUP BY title, city_id, `city_column_id`
     HAVING COUNT(*) > 1) AS b)
 AND delete_time = 0
 AND city_id > 0
 AND city_column_id > 0

将tree_news表中的重复数据进行删除(软删除)。根据title、city_id、city_column_id进行分组统计，数量大于1的说明存在重复数据，将重复数据中id最大的数据进行保留