Oracle 中用于分页的 LIMIT 和 OFFSET 的替代方案

2021-12-06 00:00:00 sql oracle sql-limit

我正在开发一个 Web 应用程序，需要对排序的结果进行分页.为此，我通常使用 LIMIT/OFFSET.

I'm developing a web application and need to page ordered results. I normaly use LIMIT/OFFSET for this purpose.

在 Oracle 中分页排序结果的最佳方法是什么?我见过一些使用 rownum 和子查询的示例.是这样吗?你能给我一个将这个 SQL 翻译成 Oracle 的示例吗:

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:

SELECT fieldA,fieldB 
FROM table 
ORDER BY fieldA 
OFFSET 5 LIMIT 14

(我使用的是 Oracle 10g，物有所值)

(I'm using Oracle 10g, for what it's worth)

谢谢！

答案:使用下面由 karim79 提供的链接，此 SQL 将如下所示:

Answer: Using the link provided below by karim79, this SQL would look like:

SELECT * FROM (
    SELECT rownum rnum, a.* 
    FROM(
        SELECT fieldA,fieldB 
        FROM table 
        ORDER BY fieldA 
    ) a 
    WHERE rownum <=5+14
)
WHERE rnum >=5

推荐答案

您将需要使用 rownum 伪列来限制结果.请看这里:

You will need to use the rownum pseudocolumn to limit results. See here:

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

相关文章