SQL 如何在 Oracle 11g 的“选择”查询中添加偏移量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27099414/
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
How to add offset in a "select" query in Oracle 11g?
提问by sasori
How to add an offset in a "select" query in Oracle 11g.
I only know how to add the limit by e.g rownum <= 5this question is not a duplicate, I already checked the other questions and are not related to mine.
如何在 Oracle 11g 的“选择”查询中添加偏移量。我只知道如何添加限制，例如rownum <= 5这个问题不是重复的，我已经检查了其他问题并且与我的无关。
So, how to add the offset in Oracle 11g ?
那么，如何在 Oracle 11g 中添加偏移量？
回答by Lalit Kumar B
You can do it easily on 12cby specifying OFFSET.
您可以12c通过指定轻松完成OFFSET。
In 12c,
在12c，
SELECT val
FROM table
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
To do the same on 11gand prior, you need to use ROWNUMtwice, inner queryand outer queryrespectively.
要做到在同一个11g和之前，你需要使用ROWNUM两次，inner query并outer query分别。
The same query in 11g,
中的相同查询11g，
SELECT val
FROM (SELECT val, rownum AS rnum
FROM (SELECT val
FROM table
ORDER BY val)
WHERE rownum <= 8)
WHERE rnum > 4;
Here OFFSETis 4.
这里OFFSET是 4。
回答by DirkNM
You can use ROW_NUMBER function for that.
您可以为此使用 ROW_NUMBER 函数。
Maybe this helps:
也许这有帮助：
SELECT *
FROM(SELECT t.*,
ROW_NUMBER() OVER (ORDER BY ...) rn -- whatever ordering you want
FROM your_table t
)
WHERE rn >= ... -- your offset
Hope that helps
希望有帮助
回答by psaraj12
Use the function LAG or LEAD in oracle
在oracle中使用LAG或LEAD函数
The LAG function is used to access data from a previous row
The LEAD function is used to return data from the next row
Usage:-
用法：-
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
Please find the this linkfor examples
请找到此链接以获取示例
