mysql - 如何删除除50个最新行以外的所有行

如何删除表中的所有行recentposts

DELETE FROM recentposts WHERE recentposts.`userId` = 12 AND recentposts.`Id`
NOT IN (SELECT * FROM recentposts WHERE `userId` = 12 ORDER BY viewdate LIMIT 50)

我尝试过很多类似的方法，但都没有成功。有人能告诉我在mysql中如何做到这一点吗？

最佳答案

这个怎么样？

DELETE FROM recentposts
WHERE
    recentposts.`userId` = 12
    AND
    recentposts.`Id` NOT IN (SELECT Id
        FROM recentposts
        WHERE `userId` = 12
        ORDER BY viewdate DESC LIMIT 50)