php - 删除最近 80 后的 MYSQL 值

Question

我目前正在使用:

SELECT * FROM users ORDER BY date_time DESC LIMIT 0, 80

要显示 80 个最近的用户名，我如何进行查询以删除超过这 80 个的任何其他条目？

我想我需要一个 cron 作业，但它需要执行什么样的查询？

Answer 1

以下是不同的方式:

delete users from users
LEFT JOIN
(
  select id from users order by date_time DESC LIMIT 0, 80) t1 ON users.id=t1.id
where t1.id is null

SQL Fiddle demo

没有限制:

delete users from users
LEFT JOIN
(
  select id from
(
SELECT  id, 
        @curRow := @curRow + 1 AS row_number
FROM    users,(SELECT @curRow := 0) r
ORDER BY date_time DESC) t where row_number<=80
) t1 ON users.id=t1.id
where t1.id is null

SQLFiddle demo