PostgreSQL FETCH 用法与实例

在 PostgreSQL 中，您可以在 SELECT 语句中使用 FETCH 子句来限定返回的行的数量以节省系统的开销。

FETCH 子句与 LIMIT 子句作用相同，但是 FETCH 子句是 SQL 标准，而 LIMIT 子句不是 SQL 标准。

PostgreSQL FETCH 语法

要使用 FETCH 子句限制返回的行数，请按照如下语法形式使用它：

FETCH { FIRST | NEXT } [ rows_count ] { ROW | ROWS } ONLY

这里：

您可以使用 FIRST 和 NEXT 中的任意一个，他们含义相同。
您可以使用 ROW 和 ROWS 中的任意一个，他们含义相同。
rows_count 是要限制的行数，即返回的最大的行数。它是可选的，默认值为 1。您应该为 rows_count 指定一个大于 0 的整数值。

带有 FETCH 子句的 SELECT 语句完整用法如下：

SELECT column_list
FROM table_name
[other_clauses]
FETCH rows_count;

其中，other_clauses 是那些可以在 SELECT 语句中使用的其他子句，比如 WHERE, ORDER BY, OFFSET 等

通常，您需要在带有 FETCH 子句的 SELECT 语句中一同使用 ORDER BY 子句，这样您可以得到一个按照指定的顺序排序的结果集。这是一个好的实践。

在一些分页的查询的应用中，您需要在 SELECT 语句结合使用 OFFSET 子句和 FETCH 子句。如下：

SELECT column_list
FROM table_name
ORDER BY ...
OFFSET skipped_rows
FETCH FIRST rows_count ROWS ONLY;

比如:

第一页可以使用： OFFSET 0 OFFSET FIRST 10 ROWS ONLY 表示最多返回 10 行。
第二页可以使用： OFFSET 10 OFFSET FIRST 10 ROWS ONLY 表示跳过第一页的 10 行后最多返回 10 行。
第三页可以使用： OFFSET 20 OFFSET FIRST 10 ROWS ONLY 表示跳过前两页的 20 行后最多返回 10 行。
以此类推…

PostgreSQL FETCH 实例

我们将使用 Sakila 示例数据库中的表进行演示，请您先在 PostgreSQL 中安装 Sakila 示例数据库。

在 Sakila 示例数据库中， film 表存储了一个 DVD 商店中的可以租赁的所有的影片。

使用 PostgreSQL FETCH 限制返回行数示例

要限制从 film 表查询时最多返回 5 行，请使用下面的带有 FETCH 子句的 SELECT 语句：

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
FETCH FIRST 5 ROWS ONLY;

 film_id |      title       | release_year
---------+------------------+--------------
       1 | ACADEMY DINOSAUR |         2006
       2 | ACE GOLDFINGER   |         2006
       3 | ADAPTATION HOLES |         2006
       4 | AFFAIR PREJUDICE |         2006
       5 | AFRICAN EGG      |         2006

使用 PostgreSQL FETCH 获取前 N 行

通常，您经常使用 FETCH 子句从表中选择具有最高或最低值的行。

例如，要获得租金最高的 10 部电影，您可以按租金降序对电影进行排序，然后使用 FETCH 子句获得前 10 部电影。您可以通过以下查询达到这个目的：

SELECT
  film_id,
  title,
  rental_rate
FROM film
ORDER BY rental_rate DESC, film_id
FETCH FIRST 10 ROWS ONLY;

查询结果如下：

 film_id |        title         | rental_rate
---------+----------------------+-------------
       2 | ACE GOLDFINGER       |        4.99
       7 | AIRPLANE SIERRA      |        4.99
       8 | AIRPORT POLLOCK      |        4.99
      10 | ALADDIN CALENDAR     |        4.99
      13 | ALI FOREVER          |        4.99
      20 | AMELIE HELLFIGHTERS  |        4.99
      21 | AMERICAN CIRCUS      |        4.99
      28 | ANTHEM LUKE          |        4.99
      31 | APACHE DIVINE        |        4.99
      32 | APOCALYPSE FLAMINGOS |        4.99

这里，为了获取租金最高的 10 不影片，我们使用了 ORDER BY rental_rate DESC, film_id 子句对影片按照租金逆序排序，并使用 FETCH FIRST 10 ROWS ONLY 子句获取前 10 行。

使用 PostgreSQL FETCH 和 OFFSET 分页查询示例

film 表中共有 1000 行关于影片的信息。您可以通过以下带有 COUNT(*) 表达式的 SELECT 语句得到验证：

SELECT COUNT(*) FROM film;

 count
-------
  1000

假设你有一个系统需要在前端展示所有的影片信息，将 1000 行信息全部展示在一页上并不是一个好的方案。因为这个方案如下的缺点：

数据库性能。一个语句中返回大量的数据会带给数据库服务器更大的内存开销和 IO 消耗。
应用性能。大量的数据会导致应用占用更大的内存，甚至回导致应用卡顿、卡死。
用户的体验。用户面对大量的数据会眩晕。

一个更好的方案是分页显示所有的影片。您可以很轻松地使用带有 FETCH 和 OFFSET 子句的 SELECT 语句实现分页查询。

假设您需要每页显示 10 个影片信息，那么您可以使用如下的语句获取第一页的所有行：

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
FETCH FIRST 10 ROWS ONLY;

这里，为了让所有分页的顺序一致，我们使用 ORDER BY film_id 让影片按照 film_id 排序，并使用 FETCH FIRST 10 ROWS ONLY 限制了此查询最多返回 10 行。

 film_id |      title       | release_year
---------+------------------+--------------
       1 | ACADEMY DINOSAUR |         2006
       2 | ACE GOLDFINGER   |         2006
       3 | ADAPTATION HOLES |         2006
       4 | AFFAIR PREJUDICE |         2006
       5 | AFRICAN EGG      |         2006
       6 | AGENT TRUMAN     |         2006
       7 | AIRPLANE SIERRA  |         2006
       8 | AIRPORT POLLOCK  |         2006
       9 | ALABAMA DEVIL    |         2006
      10 | ALADDIN CALENDAR |         2006

为了获取第二页要显示的 10 行，我们使用 OFFSET 10 子句跳过第一页的 10 行，并使用 FETCH FIRST 10 ROWS ONLY 限制了此查询最多返回 10 行。您可以使用如下的语句获取第二页的所有行：

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
OFFSET 10
FETCH FIRST 10 ROWS ONLY;

 film_id |        title        | release_year
---------+---------------------+--------------
      11 | ALAMO VIDEOTAPE     |         2006
      12 | ALASKA PHANTOM      |         2006
      13 | ALI FOREVER         |         2006
      14 | ALICE FANTASIA      |         2006
      15 | ALIEN CENTER        |         2006
      16 | ALLEY EVOLUTION     |         2006
      17 | ALONE TRIP          |         2006
      18 | ALTER VICTORY       |         2006
      19 | AMADEUS HOLY        |         2006
      20 | AMELIE HELLFIGHTERS |         2006

同样的，您可以使用如下的语句获取第三页的所有行：

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
OFFSET 20
FETCH FIRST 10 ROWS ONLY;

这里，我们使用 OFFSET 20 指示了跳过前两页的 20 行，并使用 FETCH FIRST 10 ROWS ONLY 限制了此查询最多返回 10 行。

结论

在本文中，您学习了如何使用 PostgreSQL FETCH 子句限制 SELECT 语句返回的行数。

FETCH 子句和 ORDER BY 子句用于解决 TOP N 问题。
FETCH 子句，OFFSET 子句和 ORDER BY 子句用于解决分页查询问题。

PostgreSQL FETCH 语法

PostgreSQL FETCH 实例

使用 PostgreSQL FETCH 限制返回行数示例

使用 PostgreSQL FETCH 获取前 N 行

使用 PostgreSQL FETCH 和 OFFSET 分页查询示例

结论

本页目录