限制 SQL 返回行
您可以通过特定的行数或行的百分比来限制从 SQL 查询返回的行。在某些情况下，您可能需要在返回的行数受到限制之前对查询结果进行排序。
首先对其行进行排序，然后限制返回的行数的查询通常称为 Top-N 查询，它提供了一种创建报告的简单方法或只是基本问题的简单视图，例如“薪水最高的10个员工是谁？” 它对于提供数据集的前几行以供浏览的用户界面也很有用。当您发出 Top-N 查询时，您可能还需要指定一个偏移量：该偏移量不包括查询结果集的前导行。然后，查询返回从偏移后的第一行开始的指定行数或百分比。偏移量使您能够修改典型问题，以便有关最高薪员工的问题可能会跳过前十名员工，而仅返回薪资排名中第十一位到第二十位的员工。以类似的方式，您可以按工资查询员工，跳过前十名员工，然后返回剩余员工的前 10%。
在相当长的时间内，一直都是使用ROW_NUMBER窗口函数、ROWNUM伪列和其它技术来实现，但现在在 Oracle 21c 之后可以使用 row_limiting_clause 的 ANSI SQL 标准语法更简单地编写。使用此子句时，您可以通过在查询中包含 ORDER BY 子句来确保 Top-N 查询所需的确定性排序顺序。row_limiting_clause 子句显示为 SELECT 的最后一部分，位于 ORDER BY 子句之后，并且以关键字 FETCH 或 OFFSET 开头。其基本语法如下：
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
OFFSET
OFFSET是偏移量，用于指定在行限制开始之前要跳过的行数。偏移量必须是一个数字。如果指定负数，则 offset 将被视为 0。如果指定 NULL 或大于或等于查询返回的行数的数字，则返回 0 行。如果 offset 包含小数，则小数部分将被截断。如果不指定此子句，则偏移量为 0，并且行限制从第一行开始。为了提高可读性，Oracle 提供了使用 ROW 或 ROWS 的选项 - 两者是等效的。
FETCH
用于指定要返回的行数或行的百分比。如果不指定此子句，则返回从 offset + 1 行开始的所有行。如果您使用WITH TIES 关键字，您的查询还将包括与最后一个合格行的排序键匹配的所有行。
为了说明如何限制查询中返回的行数，请考虑以下语句：
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
在此语句中，将返回 employee_id 值最小的前 5 名员工。
要返回下一组 5 名员工，请在语句中添加 OFFSET：
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
在这个语句中，FETCH FIRST 和 FETCH NEXT 是等价的，但是使用 OFFSET 时 FETCH NEXT 更清晰。
偏移量可以是一个更大的值，比如10，如下语句：
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
EMPLOYEE_ID LAST_NAME
----------- ----------
110 Chen
111 Sciarra
112 Urman
113 Popp
114 Raphaely
您可以选择按百分比而不是固定数字返回值。为了说明这一点，以下语句返回工资最低的 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
在此结果集中，5% 是六行。如果您使用 OFFSET，这一点很重要，因为百分比计算基于应用偏移之前的整个结果集。使用 OFFSET 的示例如下：
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary, employee_id
OFFSET 6 ROWS FETCH FIRST 5 PERCENT ROWS ONLY;
EMPLOYEE_ID LAST_NAME SALARY
----------- --------------------- ------
131 Marlow 2500
140 Patel 2500
144 Vargas 2500
182 Sullivan 2500
191 Perkins 2500
118 Himuro 2500
该语句仍返回六行，但它是从结果集的第七行开始。添加到 ORDER BY 子句的附加雇员 ID 是为了保证确定性排序。
您可以选择使用WITH TIES 返回关系值。这将返回工资最低的 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
您可以使用以下语句发出相同的查询，并跳过前 5 个值：
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
OFFSET 5 ROWS FETCH FIRST 5 PERCENT ROWS WITH TIES;
EMPLOYEE_ID LAST_NAME SALARY
----------- --------------------- ------
119 Colmenares 2500
131 Marlow 2500
140 Patel 2500
144 Vargas 2500
182 Sullivan 2500
191 Perkins 2500SQL 行限制限制和注意事项：
row_limiting_clause 子句受到以下限制：
您不能使用 for_update_clause 指定此子句。 如果指定此子句，则选择列表不能包含序列伪列 CURRVAL 或 NEXTVAL。 如果定义查询包含此子句，则物化视图不符合增量刷新的条件。
腾讯云开发者
