SQL (ORACLE)：ORDER BY 和 LIMIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7480243/
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
SQL (ORACLE): ORDER BY and LIMIT
提问by DraggonZ
I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET.
我想按属性对我的数据库中的所有数据进行排序，并且仅在使用 LIMIT 和 OFFSET 之后进行排序。
Query like this:
像这样查询：
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]
I know the sorting ends as soon as it has found the first row_count rows of the sorted result. Can I do sorting all data before calling LIMIT and OFFSET?
我知道排序一旦找到排序结果的第一个 row_count 行就结束。我可以在调用 LIMIT 和 OFFSET 之前对所有数据进行排序吗？
回答by Justin Cave
Prior to 12.1, Oracle does not support the LIMITor OFFSETkeywords. If you want to retrieve rows N through M of a result set, you'd need something like:
在 12.1 之前，Oracle 不支持LIMITorOFFSET关键字。如果要检索结果集的 N 到 M 行，则需要以下内容：
SELECT a.*
FROM (SELECT b.*,
rownum b_rownum
FROM (SELECT c.*
FROM some_table c
ORDER BY some_column) b
WHERE rownum <= <<upper limit>>) a
WHERE b_rownum >= <<lower limit>>
or using analytic functions:
或使用解析函数：
SELECT a.*
FROM (SELECT b.*,
rank() over (order by some_column) rnk
FROM some_table)
WHERE rnk BETWEEN <<lower limit>> AND <<upper limit>>
ORDER BY some_column
Either of these approaches will sort give you rows N through M of the sorted result.
这些方法中的任何一种都会为您提供排序结果的 N 到 M 行。
In 12.1 and later, you can use the OFFSETand/or FETCH [FIRST | NEXT]operators:
在 12.1 及更高版本中，您可以使用OFFSET和/或FETCH [FIRST | NEXT]运算符：
SELECT *
FROM some_table
ORDER BY some_column
OFFSET <<lower limit>> ROWS
FETCH NEXT <<page size>> ROWS ONLY
