Oracle/SQL - 选择指定范围的连续记录

发布于 2024-10-15 05:25:07 字数 1180 浏览 6 评论 0原文

我正在尝试从联接中选择记录子集（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

因此，当我运行此查询时，我从我们的系统收到此消息（这不是 Oracle cli 界面，而是存在于其之上的 Web 层，因此如果异常，请直接显示错误消息）

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

资源将是查询的结果。

那么有人知道发生了什么或者有更好的方法吗？

谢谢！

原文

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.

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)

'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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

盛夏尉蓝 2024-10-22 05:25:07

我相信您正在 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
/

Ask Tom 的良好讨论是此处

I believe you're looking for something like this in 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

回复收藏 0 原文

~没有更多了~