协慌网

 登录 贡献 社区

如何在订购后限制 Oracle 查询返回的行数？

有没有办法让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

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

答案

您可以使用子查询

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

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

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

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 的暗示。

从 Oracle 12C R1（12.1）开始， 有一个行限制性条款 。它不使用熟悉的LIMIT语法，但它可以通过更多选项更好地完成工作。您可以在此处找到完整语法 。

要回答原始问题，这是查询： 

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

（对于早期的 Oracle 版本，请参阅此问题中的其他答案）

例子：

以下示例引用链接页面 ，以防止链接腐烂。

建立

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行有关系，则获取所有绑定的行

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.

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

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 秒