mysql - 删除索引高于 X 且具有相同值的行

Question

我有每个帐户的页面，显示最近 10 次登录以及 IP 和日期。我只需要每个帐户的最后 10 个，因此我想删除具有相同 account_id 值的第 11、12、13 行。我的表结构:

+----+------------+------------+------------+
| id | account_id |    ipv4    | timestamp  |
+----+------------+------------+------------+
| 25 |    9977930 | 1299288404 | 1362261821 |
| 26 |    9982862 | 1604359422 | 1362262365 |
+----+------------+------------+------------+

我尝试过类似的方法:

DELETE t1 FROM `lastlogin` t1
LEFT JOIN 
(
  SELECT `account_id`
  FROM `lastlogin`
  LIMIT 10 OFFSET 10
) t2 on t2.account_id = t1.account_id AND t2.value = t1.value
WHERE t2.value is null;

但是我的 select 语句是错误的，其余的也很糟糕。我没有好主意，除了为索引添加新列以及更高级 SQL 的经验之外如何做到这一点...有人可以构造这个查询吗？

Answer 1

我会使用行号函数，所以:

     Delete t1 from
       (select accountid, 
        row_number() over (partition by accountid, order by accountid,                    
       Timestamp desc) as r 
       From lastlogin ) t1 
     where t1.r > 10

希望这个语法是正确的，本质上您使用行号函数来创建一个数字，该数字按降序时间顺序为每个不同的 ip 递增，并为每个不同的帐户重置。

这假设您需要最新的 ip，并且每个 ip 都是不同的。如果 ip 不是按时间戳或 ID 划分的不同分区。首先运行选择!