mysql - SQL 在具有限制的选择查询后删除行

Question

我想从一个包含多个用户的表中删除除用户最近的 20 个测试之外的所有测试。

尝试 1:

DELETE FROM tests WHERE EXISTS (SELECT * FROM tests WHERE user_id = 38 ORDER BY test_timestamp DESC LIMIT 20, 100);

尝试 2:

DELETE FROM tests WHERE user_id = 38 ORDER BY test_timestamp DESC LIMIT 20, 100;

是否有任何更改可以让上述示例正常工作，或者我是否需要使用 PHP 设置行计数变量来解决我的偏移问题？

Answer 1

尝试:

DELETE FROM tests 
WHERE tests.user_id = 38
and tests.ID in (SELECT id 
FROM tests t
WHERE t.user_id = tests.user_id
ORDER BY test_timestamp DESC LIMIT 20, 100);