SQL Oracle 中用于分页的 LIMIT 和 OFFSET 的替代方案
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2912144/
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
Alternatives to LIMIT and OFFSET for paging in Oracle
提问by danielpradilla
I'm developing a web application and need to page ordered results. I normaly use LIMIT/OFFSET for this purpose.
我正在开发一个 Web 应用程序,需要对排序的结果进行分页。为此,我通常使用 LIMIT/OFFSET。
Which is the best way to page ordered results in Oracle? I've seen some samples using rownum and subqueries. Is that the way? Could you give me a sample for translating this SQL to Oracle:
在 Oracle 中分页排序结果的最佳方法是什么?我见过一些使用 rownum 和子查询的示例。是这样吗?你能给我一个将这个 SQL 翻译成 Oracle 的示例吗:
SELECT fieldA,fieldB
FROM table
ORDER BY fieldA
OFFSET 5 LIMIT 14
(I'm using Oracle 10g, for what it's worth)
(我正在使用 Oracle 10g,这是值得的)
Thanks!
谢谢!
Answer:Using the link provided below by karim79, this SQL would look like:
答:使用 karim79 下面提供的链接,此 SQL 将如下所示:
SELECT * FROM (
SELECT rownum rnum, a.*
FROM(
SELECT fieldA,fieldB
FROM table
ORDER BY fieldA
) a
WHERE rownum <=5+14
)
WHERE rnum >=5
采纳答案by karim79
You will need to use the rownum
pseudocolumn to limit results. See here:
您将需要使用rownum
伪列来限制结果。看这里:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
回答by Justin Cave
Since you're on 10g, you should be able to simplify the ROWNUM approach using analytic functions
由于您使用的是 10g,您应该能够使用解析函数简化 ROWNUM 方法
SELECT fieldA,
fieldB
FROM (SELECT fieldA,
fieldB,
row_number() over (order by fieldA) rnk
FROM table_name)
WHERE rnk BETWEEN 5 AND 14;
回答by Montri M
As of oracle 12c, you could use the top N queries.
从 oracle 12c 开始,您可以使用前 N 个查询。
SELECT fieldA,fieldB
FROM table
ORDER BY fieldA
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY;
http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php
http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php
回答by mijaved
Method-1: For database version Oracle12c or higher
方法一:对于数据库版本 Oracle12c 或更高版本
SELECT fieldA, fieldB
FROM table
ORDER BY fieldA
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY
Method-2: For database version Oracle11g or lower using analytical function RowNumber()
方法 2:对于 Oracle11g 或更低版本的数据库,使用解析函数 RowNumber()
SELECT fieldA, fieldB
FROM (
SELECT fieldA, fieldB,
row_number() over (order by fieldA) rowRank
FROM table_name
)
WHERE rowRank BETWEEN 5 AND 14;
Method-3: For database version Oracle11g or lower using RowNum
方法 3:对于 Oracle11g 或更低版本的数据库,使用 RowNum
SELECT T.* FROM (
SELECT T.*, rowNum as rowIndex
FROM (
SELECT fieldA, fieldB,
FROM table_name
)T)T
WHERE rowIndex > 0 AND rowIndex <= 20;
In some cases, I have found method-3 is faster than method-2 since order by clause is mandatory in method 2. However, if your database version is 12c or higher you must go for method-1.
在某些情况下,我发现方法 3 比方法 2 快,因为方法 2 中的 order by 子句是强制性的。但是,如果您的数据库版本是 12c 或更高版本,则必须使用方法 1。