MySQL:从表中删除除 20 行以外的所有行？

Question

我有一个包含数十万行的表，分配给了一个用户。例如:

itemid | userid | etc
1      | 1      | etc
2      | 1      | etc
3      | 1      | etc
4      | 3      | etc
5      | 3      | etc
6      | 3      | etc
etc    | etc    | etc

一个用户可以有任意数量的项目分配给他或她。 IE。从 0 到无穷大的任何数字。我的问题是我想要一个 SQL 查询，它将删除每个用户的所有项目，但保留 20 个。如果用户少于 20 个，例如，只分配了 10 个项目，它必须保留所有 10 个。

我该怎么做？

更新

如果用户有 50 个项目，ID 为 1 - 50，则它必须返回项目 30 - 50。换句话说，返回该用户最后插入的 20 个项目。

Answer 1

假设我们要删除除每个用户的两个最新条目之外的所有条目...

CREATE TABLE my_table(itemid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,userid INT NOT NULL);

INSERT INTO my_table VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 3),
(5, 3),
(6, 3);

SELECT * FROM my_table;
+--------+--------+
| itemid | userid |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      3 |      1 |
|      4 |      3 |
|      5 |      3 |
|      6 |      3 |
+--------+--------+

这是一个选择要删除的行的查询...

SELECT a.* 
  FROM my_table a 
  LEFT 
  JOIN 
     ( SELECT x.*, COUNT(*) FROM my_table x JOIN my_table y ON y.userid = x.userid AND y.itemid >= x.itemid GROUP BY x.itemid HAVING COUNT(*) <=2)b 
    ON b.itemid = a.itemid 
 WHERE b.itemid IS NULL;
+--------+--------+
| itemid | userid |
+--------+--------+
|      1 |      1 |
|      4 |      3 |
+--------+--------+

...这是一个删除它们的查询...

DELETE a 
  FROM my_table a 
  LEFT 
  JOIN 
     ( SELECT x.*, COUNT(*) FROM my_table x JOIN my_table y ON y.userid = x.userid AND y.itemid >= x.itemid GROUP BY x.itemid HAVING COUNT(*) <=2)b 
    ON b.itemid = a.itemid 
 WHERE b.itemid IS NULL;

SELECT * FROM my_table;
+--------+--------+
| itemid | userid |
+--------+--------+
|      2 |      1 |
|      3 |      1 |
|      5 |      3 |
|      6 |      3 |
+--------+--------+