如何在订购后限制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 
  (  ) 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 (
    
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW


分析

select * from (
  
) 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

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

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

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

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