Oracle中分页的LIMIT和OFFSET的替代方法 [英] Alternatives to LIMIT and OFFSET for paging in Oracle

问题描述

我正在开发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/technology/oramag/oracle/06-sep/o56asktom.html

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

这篇关于Oracle中分页的LIMIT和OFFSET的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！