提问



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


MySQL中,我可以这样做:


select * 
from sometable
order by name
limit 20,10


获得第21行到第30行(跳过前20行,给出下一行10)。在order by之后选择行,所以它实际上是按字母顺序从第20个名称开始的。


Oracle中,人们唯一提到的是rownum伪列,但在 order by之前对它进行了评估,这意味着:


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


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

最佳参考


从Oracle 12c R1(12.1)开始,是行限制子句。它不使用熟悉的LIMIT语法,但它可以通过更多选项更好地完成工作。你可以在这里找到完整的语法。[47] [48]


要回答原始问题,请查询以下内容:


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


(对于早期的Oracle版本,请参阅此问题中的其他答案)





示例:



下面的例子引用了链接页面,以防止链接腐烂。[49]


设置



CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;


桌子上有什么?



SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.


获取第一N



SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.


获取第一N行,如果N th 行有关系,则获取所有绑定的行



SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.


x%



SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.


使用偏移量,对分页非常有用



SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.


您可以将偏移量与百分比组合



SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

其它参考1


您可以使用子查询


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


还可以查看主题On ROWNUM并在Oracle/AskTom上限制结果以获取更多信息。[50]


更新:
为了限制下限和上限的结果,事情会变得更加膨胀


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文章复制)


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


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


有关更多示例,请参阅此答案。感谢Krumia提示。

其它参考2


我对以下方法进行了一些性能测试:


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万条记录,排序在未编入索引的日期时间行:



  • 解释计划显示所有三个选择(323168)的相同值

  • 但获胜者是AskTom(分析紧随其后)



选择前10行:



  • AskTom:28-30秒

  • 分析:33-37秒

  • 短替代方案:110-140秒



选择100,000到100,010之间的行:



  • AskTom:60秒

  • 分析:100秒



选择9,000,000到9,000,010之间的行:



  • AskTom:130秒

  • 分析:150秒


其它参考3


只有一个嵌套查询的分析解决方案:


SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;


Rank()可以替代Row_Number(),但如果名称有重复值,则可能会返回比预期更多的记录。

其它参考4


在Oracle 12c上(参见SQL参考中的行限制子句):[52]


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

其它参考5


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


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


ROWNUM是一个伪列,让很多人陷入困境。 ROWNUM值不会永久分配给一行(这是一种常见的误解)。实际分配ROWNUM值时可能会造成混淆。 ROWNUM值在通过查询的过滤谓词后分配给行,但在查询汇总或排序之前


更重要的是,ROWNUM值仅在分配后才会增加。


这就是followin查询不返回任何行的原因:


 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博客的文章中了解有关分页查询的更多信息:[53]



  • Oracle ROWNUM解释

  • 前N个和分页查询


其它参考6


少SELECT语句。此外,性能消耗较少。致积于:anibal@upf.br [54] [55]


SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

其它参考7


如果您不在Oracle 12C上,则可以使用如下所示的TOP N查询。


SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;


您甚至可以从with子句中的子句移动它,如下所示


WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;


实际上,我们创建了一个内联视图,并将rownum重命名为rnum。您可以在主查询中使用rnum作为过滤条件。

其它参考8


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

其它参考9


我v开始准备Oracle 1z0-047考试,经过12c验证
在准备它时,我遇到了一个名为FETCH FIRST的12c增强功能
它使您可以根据自己的方便获取行/限制行。
有几个选项可供选择


- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY


例:


Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

其它参考10


在oracle


SELECT val FROM   rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY;


VAL



    10
    10
     9
     9
     8


选择了5行。


SQL>

其它参考11


(未经测试)这样的事可能会起作用


WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order


还有分析功能等级,您可以使用它来排序。

其它参考12


与上述相同,有更正。工作,但绝对不漂亮。


   WITH
    base AS
    (
        select *                   -- get the table
        from sometable
        order by name              -- in the desired order
    ),
    twenty AS
    (
        select *                   -- get the first 30 rows
        from base
        where rownum <= 30
        order by name              -- in the desired order
    )
    select *                       -- then get rows 21 .. 30
    from twenty
    where rownum < 20
    order by name                  -- in the desired order


老实说,最好使用上面的答案。