SQL> select * from
(select rownum rnum,id from (select * from btable order by id desc) where rownum < 1000006
) where rnum > 1000000 ; RNUM ID
---------- ----------
1000001 999999
1000002 999998
1000003 999997
1000004 999996
1000005 999995 已用时间: 00: 00: 02.63select * from
(select row_number() over(order by id) rnum from btable) where rnum < 1900006 and rnum > 1900000; RNUM
----------
1900001
1900002
1900003
1900004
1900005 已用时间: 00: 00: 02.38
(select rownum rnum,id from (select * from btable order by id desc) where rownum < 1000006
) where rnum > 1000000 ; RNUM ID
---------- ----------
1000001 999999
1000002 999998
1000003 999997
1000004 999996
1000005 999995 已用时间: 00: 00: 02.63select * from
(select row_number() over(order by id) rnum from btable) where rnum < 1900006 and rnum > 1900000; RNUM
----------
1900001
1900002
1900003
1900004
1900005 已用时间: 00: 00: 02.38
select * from btable order by id 要替换成 select * from btable group by id,.....
insert into t_test values(30);
insert into t_test values(10);
insert into t_test values(0);
insert into t_test values(5);
insert into t_test values(25);
insert into t_test values(20);
select * from t_test;
30
10
0
5
25
20
select * from (select rownum rn,t_test.id from t_test where id <20 order by id)
where rn between 2 and 3;
0
5
如各位所说,使用了order by以后,这个结果是错误的,应该是
5
10才对。create view v_test as select t_test.id from t_test where id <20 order by id;select * from (select rownum rn,v_test.* from v_test )
where rn between 2 and 3;
5
10
在使用了view以后,order by 就按照预想的结果排列了。而我的程序要访问的对象就是view,所有的where 和order by都只出现在view中。以上,是我在ORACLE8i中的实验结果,可否帮忙在oracle7中再帮我试验一下,多谢。
帖子我也会再加分的。