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

问题描述

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

推荐答案

从 Oracle 12c R1 (12.1) 开始，有一个行限制条款.它不使用熟悉的 LIMIT 语法，但它可以通过更多选项更好地完成工作.您可以在此处找到完整的语法.(另请阅读此答案中有关 Oracle 内部如何工作的更多信息).

Starting from Oracle 12c R1 (12.1), there is a row limiting clause. It does not use familiar LIMIT syntax, but it can do the job better with more options. You can find the full syntax here. (Also read more on how this works internally in Oracle in this answer).

要回答原始问题，请输入以下查询:

To answer the original question, here's the query:

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

(对于较早的 Oracle 版本，请参考此问题中的其他答案)

(For earlier Oracle versions, please refer to other answers in this question)

以下示例引用自 链接页面，希望防止链接腐烂.

Following examples were quoted from linked page, in the hope of preventing link rot.

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行有并列，则获取所有并列的行

Get first N rows, if N^th row has ties, get all the tied rows

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.

Top 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.

这篇关于订购后如何限制 Oracle 查询返回的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！