mysql - 偏移量问题

Question

我最近发布了一个关于以正确顺序在表中获取最后 3 个结果的问题。我现在想要以正确的顺序获取除最后 3 条评论之外的所有评论。

这是我的语法；

SELECT *
FROM (SELECT * 
      FROM $table 
      ORDER BY ID DESC 
      OFFSET 3) AS T 
ORDER BY TIME_STAMP

我收到的错误是:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET, 3) AS T ORDER BY TIME_STAMP' at line 1

我似乎无法让它工作。非常感谢任何帮助。

Answer 1

根据MySQL Documentation :

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

他们建议您使用如下查询:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

所以在你的情况下，你应该尝试:

SELECT *
FROM (SELECT * 
      FROM $table 
      ORDER BY ID DESC 
      LIMIT 3,18446744073709551615) AS T 
ORDER BY TIME_STAMP

请注意，您还可以使用关键字 OFFSET 来使用 PostgreSQL 兼容版本:

SELECT *
FROM (SELECT * 
      FROM $table 
      ORDER BY ID DESC 
      LIMIT 18446744073709551615 OFFSET 3) AS T 
ORDER BY TIME_STAMP

以防万一你想知道，18446744073709551615 = 2^64 - 1。