问题描述
我正在开发一个 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
这篇关于Oracle 中用于分页的 LIMIT 和 OFFSET 的替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！