Oracle/SQL - 选择指定范围的顺序记录 [英] Oracle/SQL - Select specified range of sequential records
问题描述
我试图从连接中选择记录的子集,从 5000 到 10000.过去我曾遇到过这样的查询,但它们的复杂程度略低.这是我尝试使用的查询,如果我删除 rownum/rnum 引用(以及外部选择),我会按预期收到所有记录,因此我知道逻辑很好.
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
因此,当我运行此查询时,我从我们的系统收到此消息(这不是 Oracle cli 接口,而是存在于其顶部的 Web 层,因此如果它异常,请忽略错误 msg)
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)
'错误:选择查看的资源无效.在查看其数据之前,您可能需要重新创建或修复该对象.'
资源将是查询的结果.
那么有没有人知道发生了什么或有更好的方法来做到这一点?
So does anyone have an idea of whats going on or a better way to do this?
谢谢!
推荐答案
我相信您正在 Oracle 中寻找这样的东西:
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
/
来自 Ask Tom 的好讨论是 这里
Good discussion from Ask Tom is here
这篇关于Oracle/SQL - 选择指定范围的顺序记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!