Postgresql 简明教程

PostgreSQL - LIMIT Clause

PostgreSQL LIMIT 子句用于限制 SELECT 语句返回的数据量。

The PostgreSQL LIMIT clause is used to limit the data amount returned by the SELECT statement.

Syntax

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

The basic syntax of SELECT statement with LIMIT clause is as follows −

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

当 LIMIT 子句与 OFFSET 子句一起使用时，语法如下：

The following is the syntax of LIMIT clause when it is used along with OFFSET clause −

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

LIMIT 和 OFFSET 允许您仅检索由查询的其余部分生成的一部分行。

LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query.

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下是一个示例，该示例根据您想要从表中获取的行数限制表中的行：

The following is an example, which limits the row in the table according to the number of rows you want to fetch from table −

testdb=# SELECT * FROM COMPANY LIMIT 4;

这将产生以下结果 -

This would produce the following result −

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

但是，在某些情况下，您可能需要从特定偏移量中提取一组记录。以下是一个示例，它从第三个位置开始提取三条记录：

However, in certain situation, you may need to pick up a set of records from a particular offset. Here is an example, which picks up three records starting from the third position −

testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

这将产生以下结果 -

This would produce the following result −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)