rownum 是在查询出结果并排序完成之后给出的附加行号
select * from t where rownum = 1 and id < 3 order by id desc 应该得到2
select * from t where rownum = 1 order by id desc 4
select * from t where rownum = 1 and id < 3 order by id desc 应该得到2
select * from t where rownum = 1 order by id desc 4
很显示不是你所想:select * from t where rownum = 1 and id < 3 order by id desc 的结果是1,不是2,我想得到2
得到的结果为1错,应该是2
的结果是1 很奇怪
oracle是先给rownum 后order by,如果是这样的话,为何
select * from t where rownum = 1 order by id desc的结果为4,却不为1?我的环境是Oracle817
我在我这系统的里用这个方法查询,等得到我在1楼给出的结果
但是我自己重新建了一个表之后,得到的结果就完全是按照ROWID 排的
正在寻找区别。
--
SQL> select * from test1 where rownum=1 order by f1 desc;F1 F2 F3
---------- ---------- ----------
1 aa qq为什么没有和你们验证的那样=4呢
的结果是1
select * from t where rownum = 1 where id < 3 order by id desc
得到的结果为1
羞愧的离去……
如果有rownum条件的话,Oracle会先查出指定条数的记录.至于查出来的是哪些记录,应该与物理上的存储顺序有关,而与记录的内容没有直接的关系.查出指定条数的记录之后再对结果进行排序.
select * from tablename where rownum != 10;返回的是前9条记录。
不能用:>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件 只能用
select *
from table_name
where rownum < N
minus
select *
from table_name
where rownum < M就可以选中 M 与 N之间的。rownum是伪列,代表已经查询到的记录的行序号。还未查到的记录是没有rownum的。
因此ROWNUM>=1 AND ROWNUM<=10是可以查到前10条记录但是不能证明
ROWNUM>=500 AND ROWNUM<=600可以查处500~600的501条记录。
ROWNUM>=1 AND ROWNUM<=10在逻辑上相当于ROWNUM<=10
正确的方法是:
select * from (select Z_YXJK_DMDH.*, rownum as rid from Z_YXJK_DMDH where rownum<=600) where rid > 500
或者
select * from Z_YXJK_DMDH where rownum<=600
minus
select * from Z_YXJK_DMDH where rownum<=500
后来就改成分层的了。理论上我也不清楚它们的执行优先级别,我猜想:伪列与order by都用到类似堆栈的东东,
那样就与写的位置有关了。to:libin_ftsafe(子陌红尘:当libin告别ftsafe)
你的回答好像不到位。请高人说明具体的理论根据吧!
where -> rownum -> order by也就是说,在应用order by 前得到记录可能是随机的,
也可能受存储情况、索引方式和优化策略影响
select MAX( b.rowid) KEEP (DENSE_RANK LAST ORDER BY id ) "Best"
FROM t b where b.id <3
)
test1中有顺序添加的4条记录 1\2\3\4
--
SQL> select * from test1 where rownum<2 order by f1;
F1 F2 F3
---------- ---------- ----------
1 aa 33
SQL> select * from test1 where rownum=1 order by f1 desc;
F1 F2 F3
---------- ---------- ----------
1 aa qq按照这个结果,应该和大部分资料所讲述的一样,rownum在ORACLE查询出结果后、排序之前生成
但是我在另一个表(某系统中一个表):却得到了不一样的结果
SQL> select rid from t_fix_communication where rownum<2 and rid<'1001' order by rid;
RID
------------
1000
SQL> select rid from t_fix_communication where rownum<2 and rid<'1001' order by rid desc;
RID
------------
100099(说明:这个RID是主键+索引列)于是,我在表test1上也添加同类型(users)索引,查询结果还是没发生改变
请问这是什么原因,跟建表时的什么东东有关系?---一个比好有谱的解答:
rid上走索引有关,oracle最常见的表是关系堆表,动态的看,行无先后顺序。所以这里
select /*+full (a) */ rid from t_fix_communication a where ...
(并且此表在你测试期间没有人修改,此时你能看到跟你test一样的结果 用asc跟desc排序结果一样)
但是如果 and rid<'1001'此条件oracle使用索引范围扫描的话结果就不一样了,他返回的结果集顺序是与索引读取顺序有关。想知道oracle先返回哪些行,你可以改变rownum<10,然后分别使用全表full扫描,在使用索引范围扫描,看看结果对比一下。
要知道rownum<2只是取得到的结果集的第一行,如上两种返回的结果集顺序不一样,就导致你所说的情况的。
请执行如下查看结果:
select a.rid from t_fix_communication a
where rownum<10
and a.rid<'1001'
order by a.rid desc;
select a.rid from t_fix_communication a
where rownum<10
and a.rid<'1001'
order by a.rid ;
select /*full (a)*/a.rid from t_fix_communication a
where rownum<10
and a.rid<'1001'
order by a.rid desc;
select /*full (a)*/ a.rid from t_fix_communication a
where rownum<10
and a.rid<'1001'
order by a.rid ;
先对rowid有个感官认识:SQL> select ROWID from Bruce_test where rownum<2; ROWID
------------------ ----------
AAABnlAAFAAAAAPAAA ------------------------------------------------------------
下面来争对楼主的问题做一个测试就明白问题的所在
造成楼主困惑的问题的原因:
就是id=4的记录在id=1的记录之前插入的,
即id=4的记录rowid < id=1的记录rowid--for example:
1.现测试id=1的记录在id=4的记录前先插入的情况
QL> create table test_tab (id number);Table createdSQL> insert into test_tab values (1);1 row insertedSQL> insert into test_tab values (2);1 row insertedSQL> insert into test_tab values (3);1 row insertedSQL> insert into test_tab values (4);1 row insertedSQL> commit;Commit completeSQL> select * from test_tab; ID
----------
1
2
3
4
----楼主的测试语句
SQL> select * from test_tab where rownum = 1 and id < 3 order by id desc; ID
----------
1SQL> select * from test_tab where rownum = 1 order by id desc; ID
----------
1---可以发现在这种情况下能满组楼主的要求
---下面我们来看看ROWID的情况:
SQL> select rowid,id from test_tab where rownum = 1 and id < 3 order by id desc;ROWID ID
------------------ ----------
AAAOH+AALAAALr8AAA 1SQL> select rowid,id from test_tab where rownum = 1 order by id desc;ROWID ID
------------------ ----------
AAAOH+AALAAALr8AAA 1SQL> select rowid,id from test_tab;ROWID ID
------------------ ----------
AAAOH+AALAAALr8AAA 1 --ID=1的记录的ROWID最小表明是最早生成的数据
AAAOH+AALAAALr8AAB 2
AAAOH+AALAAALr8AAC 3
AAAOH+AALAAALr8AAD 4 --ID=4的记录的ROWID最大表明是最晚生成的数据--从中可以看出实际取的是MIN(ROWID)=AAAOH+AALAAALr8AAA的记录2.再测试id=4的记录在id=1的记录前先插入的情况SQL> drop table test_tab;Table droppedSQL> create table test_tab (id number);Table createdSQL> insert into test_tab values (4);1 row insertedSQL> insert into test_tab values (1);1 row insertedSQL> insert into test_tab values (2);1 row insertedSQL> insert into test_tab values (3);1 row inserted--楼主的测试语句
SQL> select * from test_tab where rownum = 1 and id < 3 order by id desc; ID
----------
1SQL> select * from test_tab where rownum = 1 order by id desc; ID
----------
4
--可以看见这边就出现楼主所说的问题了
--在来看ROWID 的情况:
SQL> select rowid,id from test_tab where rownum = 1 and id < 3 order by id desc;ROWID ID
------------------ ----------
AAAOH/AALAAALr8AAB 1SQL> select rowid,id from test_tab where rownum = 1 order by id desc;ROWID ID
------------------ ----------
AAAOH/AALAAALr8AAA 4SQL> select rowid,id from test_tab;ROWID ID
------------------ ----------
AAAOH/AALAAALr8AAA 4 --ID=4的记录的ROWID最小表明是最早生成的数据
AAAOH/AALAAALr8AAB 1
AAAOH/AALAAALr8AAC 2
AAAOH/AALAAALr8AAD 3 --ID=1的记录的ROWID最大表明是最晚生成的数据
----======================================================================
综上所述:
决定rownum限定的排序查询结果的另外一个重要原因是rowid.要达到楼主的查询可以使用子查询来做
如下:
SQL> select * from (select * from test_tab order by id desc) where rownum = 1; ID
----------
4
create table TEST1
(
ID NUMBER not null, //id已设置为主键
F VARCHAR2(50)
)从小到大增加四条记录(从rowid可以看出):
select t.*,rowid from test1 t;
-----------------
ID F ROWID
1 AAAGLeAAIAAACO2AAA
2 AAAGLeAAIAAACO2AAB
3 AAAGLeAAIAAACO2AAC
4 AAAGLeAAIAAACO2AAD
select * from test1 where rownum =1 order by id desc
(这条查询为何不是id为1的行?)
--------------------------------
ID F ROWID
4 AAAGLeAAIAAACO2AAD
select t.*, rowid from test1 t where rownum =1 and id<3 order by id desc
--------------------------------
ID F ROWID
1 AAAGLeAAIAAACO2AAA
-------
ifsapp@YKKAP>select * from test_tab; ID
----------
1
2
3
4ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc; ID
----------
1ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。
--不COMMIT数据现查询就能得到楼主的那种情况
ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc; ID
----------
4ifsapp@YKKAP>commit;提交完成。
--现在再COMMIT查询结果也不变
ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc; ID
----------
4ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。ifsapp@YKKAP>commit;提交完成。--但是提交后在查询结果就不一样了
ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc; ID
----------
1发现COMMIT与不COMMIT的情况得到的情况竟然是不一样的,这个已经在我的所知范围之外,大家一起来讨论下
where rownum = 1 直接就把缺省的第一条记录给选择了出来,order by id desc只是对唯一的一条记录进行排序,由下面的测试可以得出
ifsapp@YKKAP>select * from test_tab where rownum = 1 and id < 3 ; ID
----------
1ifsapp@YKKAP>select * from test_tab where rownum = 1; ID
----------
4ifsapp@YKKAP>select * from test_tab where rownum = 1 and id < 3 order by id asc; ID
----------
1ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id asc; ID
----------
4
2.我其实没有办法得到完全和楼主一样的情况,在刚才所说的COMMIT之前与之后的查询中我发现ROWID的改变的,如下:
在不同的删除的时候可能会有不同的ROWID:
ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。ifsapp@YKKAP>select rowid,id from test_tab;ROWID ID
------------------ ----------
AAAOIGAALAAALr+AAB 1
AAAOIGAALAAALr+AAC 2
AAAOIGAALAAALr+AAD 3
AAAOIGAALAAALr+AAE 4ifsapp@YKKAP>commit;提交完成。ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc; ID
----------
1ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。ifsapp@YKKAP>commit;提交完成。ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc; ID
----------
4ifsapp@YKKAP>select rowid,id from test_tab;ROWID ID
------------------ ----------
AAAOIGAALAAALr+AAA 4
AAAOIGAALAAALr+AAB 1
AAAOIGAALAAALr+AAC 2
AAAOIGAALAAALr+AAD 3在不同的删除的时候可能会有不同的ROWID:原来ROWID也是随即分配的可前可后的连续地址??
注:我无法再现楼主你的那种情况:)我这边的确实是以ROWID排序来默认一个行数据
查询结果不是我手写的,是执行后copy出来的,这一点保证。