Results

Question

有没有一种方式可以使 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

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

Answer 1

您可以使用子查询来实现这一点。

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

请看Oracle/AskTom的主题关于ROWNUM和结果限制，获取更多信息。

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

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;

这是一篇技术文章，故我们不能提供准确翻译，请提供具体的文章链接或者更多上下文信息。

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

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

请参阅这个回答获取更多示例。感谢Krumia的提示。

Answer 2

从Oracle 12c R1（12.1）开始，有一种“行限制子句”。它不使用熟悉的"LIMIT"语法，但可以提供更多的选项来完成工作。您可以在此处找到完整的语法。（还可以在此答案中了解Oracle内部如何工作的更多信息。）

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

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

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

Examples:

以下示例摘自链接页面，希望能防止链接失效。

Setup

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

COMMIT;

What s in the table?

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.

Get first `N` rows

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

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

5 rows selected.

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`% of rows

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

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

4 rows selected.

Using an offset, very useful for pagination

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.

You can combine offset with percentages

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.

Answer 3

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

Asktom

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

Analytical

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

Short Alternative

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

Results

表格有1000万条记录，排序不在一个有索引的日期时间行上：

Explain plan showed same value for all three selects (323168)
But the winner is AskTom (with analytic following close behind)

选择前10行所花费的时间：

AskTom: 28-30 seconds
Analytical: 33-37 seconds
Short alternative: 110-140 seconds

选择在100,000和100,010之间的行：

AskTom: 60 seconds
Analytical: 100 seconds

选择介于9,000,000和9,000,010之间的行：

AskTom: 130 seconds
Analytical: 150 seconds

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()，但如果名称存在重复值，可能会返回更多记录，超出您的预期。

Answer 5

SQL Standard

自版本12c起，Oracle支持SQL:2008标准，该标准提供以下句法以限制SQL结果集：

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g and older versions

在12c版本之前，要获取Top-N记录，您必须使用派生表和ROWNUM伪列：

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

Answer 6

在 Oracle 12c 中（请参阅SQL 参考中的行限制子句）：

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

Answer 7

在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博客的文章中了解更多关于分页查询的信息：

Answer 8

作为被接受的答案的延伸，Oracle内部使用ROW_NUMBER/RANK函数。 OFFSET FETCH语法是语法糖。

可以通过使用DBMS_UTILITY.EXPAND_SQL_TEXT程序进行观察：

准备样品：

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 DESC
FETCH FIRST 5 ROWS ONLY;

是规则的。

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db<>fiddle演示

获取扩展的SQL文本：

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text =>  
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY ,
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

WITH TIES 被展开为 RANK:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text =>  
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES ,
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

和偏移量：

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text =>  
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY ,
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

Answer 9

使用21C版本，您可以简单地应用以下限制：

select * from course where ROWNUM <=10;

Answer 10

更少的SELECT语句。同时，更少的性能消耗。致谢：anibal@upf.br

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

Answer 11

I v started preparing for Oracle 1z0-047 exam, validated against 12c While prepping for it i came across a 12c enhancement known as FETCH FIRST It enables you to fetch rows /limit rows as per your convenience. Several options are available with it

- 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

Answer 12

对于查询返回的每一行，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服务器11.2.0.1.0上实施了这个。

Answer 13

您也可以使用以下查询。

SELECT "ID", "COL1", "ETC.." FROM
         tblTest
WHERE "COL1" = "test"
ORDER BY "ID" DESC
  OFFSET x ROWS
  FETCH NEXT y ROWS ONLY;

in this case you can skip first x rows and limit resault into next y rows. then you can play around with x and y inorder to paginate over your data.

Answer 14

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

Examples:

Setup

What s in the table?

Get first N rows

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

Top x% of rows

Using an offset, very useful for pagination

You can combine offset with percentages

Asktom

Analytical

Short Alternative

Results

SQL Standard

Oracle 11g and older versions

友情链接

Get first `N` rows

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

Top `x`% of rows