先: select max(rownum) from test_table; --得出最大的rownum值 假如為100的話: select testfld from (select rownum r,b.testfld from test_table b) a where a.r = 99;
在ORACLE中,子查询或者VIEW都不能带ORDER BY, 所以你的算法可能要这样子做:假如你要拿第N个大的纪录: select testfld from test_table X WHERE EXISTS (SELECT COUNT(*) FROM TEST_TABLE Y WHERE Y.TESTFLD > X.TESTFLD HAVING COUNT(*) = N) --N使你要找的纪录顺序这种查询法,如果纪录很多的话,会很慢,请注意
select testfld from test_table X WHERE EXISTS --EXIST子句用于判断下面SQL是否至少有一条纪录 (SELECT COUNT(*) -- FROM TEST_TABLE Y --| 该段取出比TESTFLD比当前纪录大的纪录总数 WHERE Y.TESTFLD > X.TESTFLD -- HAVING COUNT(*) = N) --N使你要找的纪录顺序 --对上述结果进行筛选, 只选中纪录数为N的.如果N = 1, 表示有1条纪录的TESTFLD比当前纪录大, 那当前纪录就是第2大的纪录了
当用order by & rownum时, 在where 中的rownum只能小于某个值,for example: select ... where rownum < 20 and ... order by yourfield 是对的,但是: select ... where rownum > 20 and ... order by yourfield 或者 select ... where rownum = 20 and ... order by yourfield 是不对的.当和rownum 做比较的常量是1时倒没有错误.原因如下: 如果rownum出现在where中,oracle会先按照where中除了rownum之外的条件,以及order by 等将数据取出,这时候rownum是没有值的,然后在对每一条记录逐一判断并且对rownum 付值,得到最终结果. 例如:select * from mytable where length(myfield)=3 and rownum < 7 order by myfield的执行如下: 按照length(myfield)=3 和order by myfield 取出数据,并且对每一条记录进行判断, 第一条的rownum=1,小于7,ok, as to the second record,rownum=2,less than 7,ok... so at last u get 7 (or less if the total number is less than 7). but when u modify "rownum < 7" to "rownum > 7" or "rownum = 7",it will be like this: the first record :rownum=1,rownum > 7(or rownum=7) is false,then go to the next record,rownum=1(yes,rownum is still 1),so rownum > 7(or rownum=7) is still false... util the last record. if u really want to get the seventh record,the following sql will be ok: select tbl.* from (select ttt.*,rownum as r from yourtable ttt order by yourfield) tbl where r = 7. if u wanna get those records after the 7th, the sql will be: select tbl.* from (select ttt.*,rownum as r from yourtable ttt order by yourfield) tbl where r + 7<=(select count(*) from yourtable)----------------------- sorry,type chinses is too boring
sorry,the last sql is not perfect,it should be:select tbl.* from (select ttt.*,rownum as r from yourtable ttt order by yourfield desc) tbl where r + 7<=(select count(*) from yourtable)only add a desc after the order by yourfield
when u use sub-query as temporary view[such as select * from (select a,b,c from mytable order by a)],u may use order by in the sub-query
你写的句子可以执行,但是显示的结果不对,似乎没有按testfld排序。why?
thank you.
select max(rownum) from test_table; --得出最大的rownum值
假如為100的話:
select testfld from (select rownum r,b.testfld from test_table b) a
where a.r = 99;
select testfld
from test_table X
WHERE EXISTS (SELECT COUNT(*)
FROM TEST_TABLE Y
WHERE Y.TESTFLD > X.TESTFLD
HAVING COUNT(*) = N) --N使你要找的纪录顺序这种查询法,如果纪录很多的话,会很慢,请注意
from test_table X
WHERE EXISTS --EXIST子句用于判断下面SQL是否至少有一条纪录
(SELECT COUNT(*) --
FROM TEST_TABLE Y --| 该段取出比TESTFLD比当前纪录大的纪录总数
WHERE Y.TESTFLD > X.TESTFLD --
HAVING COUNT(*) = N) --N使你要找的纪录顺序 --对上述结果进行筛选, 只选中纪录数为N的.如果N = 1, 表示有1条纪录的TESTFLD比当前纪录大, 那当前纪录就是第2大的纪录了
select ... where rownum < 20 and ... order by yourfield 是对的,但是:
select ... where rownum > 20 and ... order by yourfield 或者
select ... where rownum = 20 and ... order by yourfield 是不对的.当和rownum
做比较的常量是1时倒没有错误.原因如下:
如果rownum出现在where中,oracle会先按照where中除了rownum之外的条件,以及order
by 等将数据取出,这时候rownum是没有值的,然后在对每一条记录逐一判断并且对rownum
付值,得到最终结果.
例如:select * from mytable where length(myfield)=3 and rownum < 7 order by
myfield的执行如下:
按照length(myfield)=3 和order by myfield 取出数据,并且对每一条记录进行判断,
第一条的rownum=1,小于7,ok, as to the second record,rownum=2,less than 7,ok...
so at last u get 7 (or less if the total number is less than 7).
but when u modify "rownum < 7" to "rownum > 7" or "rownum = 7",it will be
like this:
the first record :rownum=1,rownum > 7(or rownum=7) is false,then go to the
next record,rownum=1(yes,rownum is still 1),so rownum > 7(or rownum=7) is still
false... util the last record.
if u really want to get the seventh record,the following sql will be ok:
select tbl.* from (select ttt.*,rownum as r from yourtable ttt order by
yourfield) tbl where r = 7.
if u wanna get those records after the 7th, the sql will be:
select tbl.* from (select ttt.*,rownum as r from yourtable ttt order by
yourfield) tbl where r + 7<=(select count(*) from yourtable)-----------------------
sorry,type chinses is too boring
yourfield desc) tbl where r + 7<=(select count(*) from yourtable)only add a desc after the order by yourfield
mytable order by a)],u may use order by in the sub-query