DELETE t
FROM
tableX AS t
JOIN
( SELECT timestampColumn AS ts
FROM tableX
ORDER BY ts ASC
LIMIT 1 OFFSET 199
) tlimit
ON t.timestampColumn > tlimit.ts
附加问题:为每个用户保留前 200 行(按uid列):
DELETE tt
FROM
( SELECT DISTINCT uid --- these 3 lines can be replaced
FROM tableX --- with: UserTable AS du
) AS du --- a table that you probably have
JOIN
tableX AS tt
ON tt.uid = du.uid
AND tt.timestampColumn >
( SELECT timestampColumn AS ts
FROM tableX
WHERE uid = du.uid
ORDER BY ts ASC
LIMIT 1 OFFSET 199
)
这将保留前 200 行(可能还有更多,具有相同的时间戳):
附加问题:为每个用户保留前 200 行(按
uid
列):A
(uid, timestampColumn)
对一张大桌子很有用。如果您有一个 AUTOINCREMENT 主键,并且我们可以假设您不更新时间戳,那么这应该可以工作并且相当快:
鉴于上述假设,按
id
而不是排序date
将给出完全相同的结果。我还假设您要删除比最新的 200 旧的任何内容,ORDER BY
因此DESC
.