有没有办法使一个Oracle查询行为像它包含一个MySQL限制子句?

在MySQL中,我可以这样做:

select * 
from sometable
order by name
limit 20,10

要得到第21行到第30行(跳过前20行,给出接下来的10行)。这些行是按顺序选择的,所以实际上是从第20个名字的字母顺序开始的。

在Oracle中,人们唯一提到的是rownum伪列,但它在order by之前求值,这意味着:

select * 
from sometable
where rownum <= 10
order by name

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


当前回答

SQL标准

从版本12c开始,Oracle支持SQL:2008标准,该标准提供了以下语法来限制SQL结果集:

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

Oracle 11g及更老版本

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

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

其他回答

在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博客上的文章中可以找到更多关于分页查询的信息:

Oracle ROWNUM解释 Top-N和分页查询

少一些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;

只有一个嵌套查询的解析解:

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

Rank()可以代替Row_Number(),但如果name有重复的值,则可能返回比预期更多的记录。

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

(未经测试)像这样的东西可能会起作用

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

还有一个分析函数秩,你可以用它来排序。