mysql - 当部分行在 MySQL 中是唯一的时，如何删除重复的行？

Question

我有一个表，我在其中插入来自游戏的 newsitems 并在插入时为每一行添加时间戳。

问题是每一行都随着时间戳变得唯一，我想删除重复项，所以我只有显示更改的行。

任何人都知道我该如何解决这个问题？

这是表的创建语句

CREATE TABLE `ranklist_alliance_archive` (
  `serverid` int(11) NOT NULL,
  `resetid` int(5) DEFAULT NULL,
  `rank` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `land` varchar(45) DEFAULT NULL,
  `networth` varchar(45) DEFAULT NULL,
  `tag` varchar(45) DEFAULT NULL,
  `gov` varchar(45) DEFAULT NULL,
  `gdi` varchar(45) DEFAULT NULL,
  `protection` varchar(45) DEFAULT NULL,
  `vacation` varchar(45) DEFAULT NULL,
  `alive` varchar(45) DEFAULT NULL,
  `deleted` varchar(45) DEFAULT NULL,
  `created_ts` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

行是：serverid、resetid、rank、number、name、land、networth、tag、gov、gdi、protection、vacation、alive、deleted、created_ts

标签： mysqlsqlmariadb

Answer 1

如果要选择值更改的此类行，则一种方法是：

select raa.*
from (select raa.*,
             lag(timestamp) over (order by timestamp) as prev_timestamp,
             lag(timestamp) over (partition by . . . order by timestamp) as prev_timestamp_2
      from ranklist_alliance_archive raa
     ) raa
where prev_timestamp_2 is not null or
      prev_timestamp <> prev_timestamp_2;

. . .用于您关心更改的列。

如果你想把它变成一个delete，那么你可以使用left join. 如果timestamp它实际上是唯一的，您可以使用：

delete raa
    from ranklist_alliance_archive raa left join
         (select raa.*
          from (select raa.*,
                       lag(timestamp) over (order by timestamp) as prev_timestamp,
                       lag(timestamp) over (partition by . . . order by timestamp) as prev_timestamp_2
                from ranklist_alliance_archive raa
               ) raa
          where prev_timestamp_2 is not null or
                prev_timestamp <> prev_timestamp_2
         ) raa2
         on raa2.timestamp = raa.timestamp
    where raa2.timestamp is null;