如何限制 ORACLE 查询返回的记录数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4501515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to limit the number of records returned by an ORACLE query?
提问by user471011
How to limit the number of records returned by an ORACLEquery?
如何限制ORACLE查询返回的记录数？
and
和
which way do you think is the best?
你认为哪种方式最好？
回答by
Add "where rownum <= # of entries" at the end of your query.
在查询的末尾添加“where rownum <= # of entries”。
回答by Peter
Since Oracle 12c you can also use fetch first row only.
从 Oracle 12c 开始，您还可以使用fetch first row only.
First row:
第一排：
select * from example fetch first row only;
First 10 rows:
前 10 行：
select * from example fetch first 10 rows only;
回答by Wouter
There is however a downside to this. When you want to list record in descending or ascending order you might just miss some records. Let's say you limit your return to 8 rows, then the query takes the first 8 occurrences and list them in your sort order.
然而，这有一个缺点。当您想以降序或升序列出记录时，您可能会错过一些记录。假设您将返回值限制为 8 行，然后查询会获取前 8 次出现并按您的排序顺序列出它们。
The following query will grab the firs 8 records in the table that match your criteria and sort it in your order. eg.
以下查询将获取表中符合您的条件的前 8 条记录并按您的顺序对其进行排序。例如。
select
order, order_date, booked_out_date
from
orderdb
where
booked_out_date is not null
and rownum <= 8
order by
booked_out_date;
The next query will first grab all the records that match my criteria, sort it and then it will display the first eight orders booked out in date order.
下一个查询将首先获取符合我的条件的所有记录，对其进行排序，然后将显示按日期顺序预订的前八个订单。
Select *
From
(Select
order, order_date, booked_out_date
From
orderdb
Where
booked_out_date is not null
Order By date)
Where
rownum <= 8;
If orders that were booked out fall within the first eight orders but their record in the table is ie. the 20th record, you might miss it with the first query, but the second will pick it up.
如果预订的订单属于前八个订单，但它们在表中的记录是 ie。第 20 条记录，您可能会在第一个查询中错过它，但第二个会找到它。
回答by Jé Queue
First, are you paginatingand thus requiring limited rows (which is a valid result limiter)? Rather, ask yourself if your query is too far-reaching for the task at-hand.
首先，您是否在分页并因此需要有限的行（这是一个有效的结果限制器）？相反，问问自己您的查询是否对手头的任务影响太大。
As mentioned you can use rownumto limit the result set, or wrap rownum as below for an example of pagination:
如前所述，您可以使用rownum限制结果集，或按如下所示包装 rownum 以获取分页示例：
回答by Mukesh Rai
This is working for me. You can implement this for pagination also.
这对我有用。您也可以为分页实现此功能。
SELECT
*
FROM
(
SELECT
inner_query.*,
ROWNUM rnum
FROM
(
SELECT
*
FROM
table_name
ORDER BY column_name
) inner_query
WHERE
ROWNUM <= 20
)
WHERE
rnum >= 1;
where rownumis no. of records and rnumis start position.
其中rownum为否。记录和rnum是开始位置。
