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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 06:22:01  来源:igfitidea点击:

Alternatives to LIMIT and OFFSET for paging in Oracle

sqloraclesql-limit

提问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 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。