Oracle/SQL - 选择指定范围的顺序记录

Oracle/SQL - Select specified range of sequential records

提问by dscl

I'm tryint to select a subset of records, 5000 through 10000 from a join. I've gotten queries like this to work in the past, but they were slightly less complex. Here is the query I'm trying to use and if I remove the rownum/rnum references (and therefore the outer select) I receive all my records as expected so I know that logic is good.

我试图从连接中选择记录的子集，从 5000 到 10000。过去我曾遇到过这样的查询，但它们的复杂程度略低。这是我尝试使用的查询，如果我删除 rownum/rnum 引用（以及外部选择），我会按预期收到所有记录，因此我知道逻辑很好。

SELECT      * 
    FROM    ( 
            SELECT  unique cl.riid_, 
                    rownum as rnum 
            FROM    <table 1> cl, <table 3> mil 
            WHERE   cl.opt = 0 AND 
                    (cl.st_ != 'QT' OR cl.st_ IS NULL) AND 
                    cl.hh = 0 AND 
                    cl._ID_ = mil._ID_ AND 
                    mil.TYPE in (0, 1, 2, 3, 4) AND 
                    EXISTS 
                        ( SELECT    'x' 
                            FROM    <table 2> sub 
                            WHERE   cl.ea_ = lower(sub.ea_) AND 
                                    sub.status = 0 AND 
                                    lower(sub.subscription) = 'partner' 
                        ) AND 
                    rownum <= 10000 
            ) 
    where   rnum > 5000

So when I run this query I receive this message from our system (this is not an Oracle cli interface, but rather a web layer that exists over the top of it so please bare with the error msg if it's out of the ordinary)

因此，当我运行此查询时，我从我们的系统收到此消息（这不是 Oracle cli 接口，而是存在于其顶部的 Web 层，因此如果它异常，请忽略错误消息）

'Error: The resource selected for viewing is invalid. You may need to re-create or fix the object before viewing its data.'

'错误：选择查看的资源无效。在查看其数据之前，您可能需要重新创建或修复该对象。

The resource would be the results of the query.

资源将是查询的结果。

So does anyone have an idea of whats going on or a better way to do this?

那么有没有人知道发生了什么或有更好的方法来做到这一点？

Thanks!

谢谢！

回答by tbone

I believe you're looking for something like this in Oracle:

我相信您正在 Oracle 中寻找类似的东西：

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS
/

Good discussion from Ask Tom is here

来自 Ask Tom 的精彩讨论在这里