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

Question

如何在排序后限制Oracle查询返回的行数？有没有办法Oracle查询表现为它包含MySQL limit条款？在……里面MySQL我能做到select * from sometableorder by name limit 20,10要获得第21行到第30行(跳过前20行，给出下10行)。属性之后选择行。order by所以它实际上是从第20个名字开始的，按字母顺序排列。在……里面Oracle，人们唯一提到的就是rownum伪列，但它是计算出来的。以前 order by，这意味着：select * from sometablewhere rownum <= 10order by name将返回按名称排序的10行随机集，这通常不是我想要的。它也不允许指定偏移量。

Answer 1

从Oracle 12c R1(12.1)开始，是 a 行限制子句。它不使用熟悉的LIMIT语法，但是它可以通过更多的选项来更好地完成工作。

要回答最初的问题，下面是一个查询：

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

设置

CREATE TABLE rownum_order_test (
  val  NUMBER);INSERT ALL
  INTO rownum_order_testSELECT levelFROM   dualCONNECT BY level <= 10;COMMIT;

桌子里有什么？

SELECT valFROM   rownum_order_testORDER BY val;

       VAL----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        1020 rows selected.

先得N行

SELECT valFROM   rownum_order_testORDER BY val DESCFETCH FIRST 5 ROWS ONLY;

       VAL----------
        10
        10
         9
         9
         85 rows selected.

先得N行，如果N^TH行有领带，把所有系好的行

SELECT valFROM   rownum_order_testORDER BY val DESCFETCH FIRST 5 ROWS WITH TIES;

       VAL----------
        10
        10
         9
         9
         8
         86 rows selected.

顶x行百分比

SELECT valFROM   rownum_order_testORDER BY valFETCH FIRST 20 PERCENT ROWS ONLY;

       VAL----------
         1
         1
         2
         24 rows selected.

使用偏移量，对分页非常有用。

SELECT valFROM   rownum_order_testORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL----------
         3
         3
         4
         44 rows selected.

您可以将偏移量与百分比结合起来。

SELECT valFROM   rownum_order_testORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL----------
         3
         3
         4
         4
         4 rows selected.




Answer 2

您可以为此使用子查询，如下所示

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

更新：为了限制下界和上界的结果，事情会变得更加臃肿。

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;

更新2：从Oracle 12c(12.1)开始，有一种限制行或以偏移开始的语法。

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

Answer 3

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

阿斯汤姆

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秒

Answer 4

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

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

Rank()可以代替Row_Number()但是，如果名称存在重复值，则可能返回的记录比您预期的要多。