删除 mysql 中的重复行，保留 XX 行 - Delete duplicate rows in mysql, keep XX rows

Delete duplicate rows in mysql, keep XX rows

本文关键字：保留 XX mysql 删除 | 更新日期: 2023-09-27

我想保留 10 个最新的重复行并删除所有其他行。

我正在使用下面的代码，但它删除了除一条记录之外的所有记录。

DELETE FROM `history` WHERE id NOT IN (SELECT * FROM (SELECT MIN(n.id) FROM history n GROUP BY n.url) x)`

请尝试此查询

此代码将删除所有重复行超过 10 个的重复行。

DELETE FROM `history` WHERE id NOT IN (
    SELECT * FROM (
        SELECT MIN(n.id) FROM history n GROUP BY n.url having count(n.url) > 10
    ) x 
)

试试这段代码。

DELETE
FROM
  history
WHERE
  id NOT IN
  (SELECT
    id 
   FROM
     (
      SELECT
        @num:=IF(@current=url, @num+1, 1) AS row_num, 
        @current:=url AS group_url, 
        id
      FROM
        history
      ORDER BY
        id
     ) AS `internal`
   WHERE
     row_num<=15
  )