sql - 订购后如何限制Oracle查询返回的行数？(How do I limit the number of rows returned by an Oracle query after ordering?)

Question

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

(有没有一种方法可以使Oracle查询像包含MySQL limit子句那样工作？)

In MySQL , I can do this:

(在MySQL ，我可以这样做：)

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10).

(以获得第21至第30行（跳过前20行，给出下10行）。)

The rows are selected after the order by , so it really starts on the 20th name alphabetically.

(这些行是按order by选择order by ，因此实际上按字母顺序从第20个名称开始。)

In Oracle , the only thing people mention is the rownum pseudo-column, but it is evaluated before order by , which means this:

(在Oracle ，人们唯一提到的是rownum伪列，但它在 order by 之前 order by进行评估，这意味着：)

select * 
from sometable
where rownum <= 10
order by name

will return a random set of ten rows ordered by name, which is not usually what I want.

(将返回一个随机的十行，按名称排序，这通常不是我想要的。)

It also doesn't allow for specifying an offset.

(它也不允许指定偏移量。)

  ask by Mathieu Longtin translate from so

Answer 1

You can use a subquery for this like

(您可以像这样使用子查询)

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

(还可以查看主题ROWNUM和 Oracle / AskTom上的限制结果以获得更多信息。)

Update : To limit the result with both lower and upper bounds things get a bit more bloated with

(更新 ：为了限制结果的上下限，事情变得更加with肿)

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

(（从指定的AskTom文章复制）)

Update 2 : Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

(更新2 ：从Oracle 12c（12.1）开始，有一种语法可用于限制行或从偏移量开始。)

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples.

(有关更多示例，请参见此答案 。)

Thanks to Krumia for the hint.

(感谢Krumia的提示。)