Oracle 限制行的子句

12c新特性

限制行的子句允许限制查询返回的行。可以指定偏移量，以及要返回的行数或百分比。您可以使用此子句实现Top-N报告。要获得一致的结果，请指定ORDER_by子句以确保具有确定性的排序顺序。

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]

OFFSET

使用此子句指定行限制开始前要跳过的行数。偏移量必须是数值或计算为数值的表达式。如果指定负数，则偏移量将被视为0。如果指定为空，或者指定的数字大于或等于查询返回的行数，则返回0行。如果偏移量包含小数，则小数部分被截断。如果不指定该子句，则偏移量为0，行限制从第一行开始。ROW和ROWS关键字的含义相同，同时提供单数和复数形式，会使OFFSET子句读起来更自然。

FETCH

使用此子句指定要返回的行数rowcount或百分比percent。如果不指定该子句，则返回所有行，从行号OFFSET+1开始到最后一行。FIRST和NEXT关键字的含义相同，用于澄清语义。

使用rowcount指定要返回的行数。rowcount必须是数值或计算结果为数值的表达式。如果指定一个负数，则行数将被视为0。如果行数大于行偏移量+1处开始的可用行数，则返回所有可用行。如果行数包含小数部分，则小数部分将被截断。如果rowcount为空，则返回0行。使用percent PERCENT指定要返回的选定行总数的百分比。百分比必须是数值或计算为数值的表达式。如果指定一个负数，则百分比将被视为0。如果百分比为空，则返回0行。如果不指定行数或百分比，则返回1行。ROW和ROWS关键字的含义相同，用于澄清语义。

关键字ONLY或WITH TIES应与ROW/ROWS关键字一起使用。ROW ONLY返回指定的行数或行百分比。ROW WITH TIES会通过检索上一行时使用的关键字，提取出其他行，但必须指定order by子句才有效。如果您没有指定ORDER_BY_子句，那么不会返回其他行。

 

本子句受以下限制：

不能将此子句与for_update子句一起指定。

如果指定此子句，则选择列表不能包含序列伪列currval或nextval。

如果定义查询包含行限制子句，则物化视图不符合增量刷新的条件。

如果选择列表包含具有相同名称的列，并且指定了row_limiting子句，则会发生ORA-00918错误。无论同名列在同一个表中还是在不同的表中，都会发生此错误。可以通过为同名列指定唯一的列别名来解决此问题。

 示例：

返回employees中employee_id最低的5行:

SELECT employee_id, last_name
  FROM employees
  ORDER BY employee_id
  FETCH FIRST 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        100 King
        101 Kochhar
        102 De Haan
        103 Hunold
        104 Ernst

返回employees中employee_id最低的5行后的5行:

SELECT employee_id, last_name
  FROM employees
  ORDER BY employee_id
  OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        105 Austin
        106 Pataballa
        107 Lorentz
        108 Greenberg
        109 Faviet

 返回employees中salary最低的5%行:

SELECT employee_id, last_name, salary
  FROM employees
  ORDER BY salary
  FETCH FIRST 5 PERCENT ROWS ONLY;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        132 Olson                           2100
        128 Markle                          2200
        136 Philtanker                      2200
        127 Landry                          2400
        135 Gee                             2400
        119 Colmenares                      2500

 

返回employees中salary最低的5%行及salary等于最低5%的最后一行的其他行:

SELECT employee_id, last_name, salary
  FROM employees
  ORDER BY salary
  FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        132 Olson                           2100
        128 Markle                          2200
        136 Philtanker                      2200
        127 Landry                          2400
        135 Gee                             2400
        119 Colmenares                      2500
        131 Marlow                          2500
        140 Patel                           2500
        144 Vargas                          2500
        182 Sullivan                        2500
        191 Perkins                         2500