有没有办法使一个Oracle查询行为像它包含一个MySQL限制子句?

在MySQL中,我可以这样做:

select * 
from sometable
order by name
limit 20,10

要得到第21行到第30行(跳过前20行,给出接下来的10行)。这些行是按顺序选择的,所以实际上是从第20个名字的字母顺序开始的。

在Oracle中,人们唯一提到的是rownum伪列,但它在order by之前求值,这意味着:

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

将返回一个随机的10行按名称排序的集合,这通常不是我想要的。它也不允许指定偏移量。


当前回答

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

大于数值

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

更少的价值发现

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

其他回答

在Oracle中,带有排序的分页查询非常棘手。

Oracle提供了一个ROWNUM伪列,它返回一个数字,指示数据库从表或连接视图集中选择行的顺序。

ROWNUM是一个伪列,它会给很多人带来麻烦。ROWNUM值不会永久地分配给行(这是一个常见的误解)。当实际分配ROWNUM值时,可能会令人困惑。ROWNUM值在行传递查询的过滤器谓词之后,但在查询聚合或排序之前分配给该行。

而且,ROWNUM值只有在赋值之后才会递增。

这就是为什么下面的查询不返回行:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

查询结果的第一行没有传递ROWNUM > 1谓词,因此ROWNUM不增加到2。因此,没有ROWNUM值大于1,因此,查询不返回任何行。

正确定义的查询应该是这样的:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

在Vertabelo博客上的文章中可以找到更多关于分页查询的信息:

Oracle ROWNUM解释 Top-N和分页查询

我为以下方法做了一些性能测试:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

分析

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

短的替代

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

结果

表有1000万条记录,排序在一个未索引的datetime行上:

解释计划对所有三个选择显示相同的值(323168) 但是赢家是AskTom (analytic紧随其后)

选择前10行需要:

AskTom: 28-30秒 分析型:33-37秒 短选择:110-140秒

在100,000和100,010之间选择行:

AskTom: 60秒 分析性:100秒

在9,000,000和9,000,010之间选择行:

AskTom: 130秒 分析型:150秒

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

大于数值

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

更少的价值发现

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

对于查询返回的每一行,ROWNUM伪列返回一个数字,指示Oracle从表或连接行的集合中选择该行的顺序。所选的第一行的ROWNUM为1,第二行为2,依此类推。

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100 

我已经在oracle server 11.2.0.1.0中实现了这个功能

您可以为此使用子查询

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

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

更新: 为了同时限制结果的下界和上界,事情变得有点臃肿

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-article)

更新2: 从Oracle 12c(12.1)开始,就有了限制行或从偏移量开始的语法。

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

更多的例子请看这个答案。谢谢克鲁米亚的提示。