mysql-偏移问题 [英] mysql - offset problem

问题描述

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

I recently posted a question about getting last 3 results in table in the correct order. I now want the get all comments apart from the last 3 in the correct order.

这是我的语法；

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

我收到的错误是:

您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以在第1行的'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

我似乎无法正常工作.任何帮助表示赞赏.

I can't seem to get it to work. Any help much appreciated.

推荐答案

根据 MySQL文档:

从某个特定位置检索所有行 偏移量直到结果的结尾 设置，您可以使用大量 第二个参数.这个说法 从第96行检索所有行 到最后:

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:

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

They recommend you use a query such as:

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兼容版本:

Note that you can also use the PostgreSQL compatible version using the keyword OFFSET:

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

以防万一，18446744073709551615 = 2^64 - 1.

这篇关于mysql-偏移问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！