排序后如何限制 Oracle 查询返回的行数?

2022-01-30 00:00:00 sql oracle pagination sql-limit

有没有办法让 Oracle 查询表现得像它包含 MySQL limit 子句一样?

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

在 MySQL 中，我可以这样做:

In MySQL, I can do this:

select * 
from sometable
order by name
limit 20,10

获取第 21 到第 30 行(跳过前 20 行，给出接下来的 10 行).这些行是在 order by 之后选择的，因此它实际上按字母顺序从第 20 个名称开始.

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

在 Oracle 中，人们唯一提到的是 rownum 伪列，但它是在 before order by 计算的，这意味着:

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

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.

推荐答案

你可以为此使用子查询

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

也看看主题 关于 ROWNUM 和限制结果 在 Oracle/AskTom 了解更多信息.

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

更新:为了用上下界限制结果，事情变得有点臃肿

Update: To limit the result with both lower and upper bounds things get a bit more bloated 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;

(从指定的 AskTom 文章复制)

(Copied from specified AskTom-article)

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

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

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

有关更多示例，请参阅此答案.感谢 Krumia 的提示.

See this answer for more examples. Thanks to Krumia for the hint.

相关文章