OFFSET 子句

从结果集的开头忽略指定的行数。

语法

OFFSET rows

参数

start‑row

指定要包含在结果集中的第一行。前面的所有行都将被忽略。

依赖关系

• 将 ORDER BY 子句与 OFFSET 一起使用。否则，查询将返回结果集的一个不确定子集。

• 在 SELECT 语句或 UNION 子句中，OFFSET 必须跟在 ORDER BY 子句之后。

• 当 SELECT 语句或 UNION 子句同时指定 LIMIT 和 OFFSET 时，Vertica 首先处理 OFFSET 语句，然后将 LIMIT 语句应用于其余行。

示例

以下查询从 customer_dimension 表中返回 14 行：

=> SELECT customer_name, customer_gender FROM customer_dimension
   WHERE occupation='Dancer' AND customer_city = 'San Francisco' ORDER BY customer_name;
    customer_name     | customer_gender
----------------------+-----------------
 Amy X. Lang          | Female
 Anna H. Li           | Female
 Brian O. Weaver      | Male
 Craig O. Pavlov      | Male
 Doug Z. Goldberg     | Male
 Harold S. Jones      | Male
 Jack E. Perkins      | Male
 Joseph W. Overstreet | Male
 Kevin . Campbell     | Male
 Raja Y. Wilson       | Male
 Samantha O. Brown    | Female
 Steve H. Gauthier    | Male
 William . Nielson    | Male
 William Z. Roy       | Male
(14 rows)

如果修改上一个查询以指定偏移量 8 (OFFSET 8)，Vertica 将跳过上一个结果集的前八行。查询会返回以下结果：

=> SELECT customer_name, customer_gender FROM customer_dimension
   WHERE occupation='Dancer' AND customer_city = 'San Francisco' ORDER BY customer_name OFFSET 8;
   customer_name   | customer_gender
-------------------+-----------------
 Kevin . Campbell  | Male
 Raja Y. Wilson    | Male
 Samantha O. Brown | Female
 Steve H. Gauthier | Male
 William . Nielson | Male
 William Z. Roy    | Male
(6 rows)