SELECT fieldName
FROM
(
SELECT fieldName, ROW_NUMBER() OVER (ORDER BY fieldName) r FROM myTable
)
WHERE r >= start_row AND r <= end_row; Note: require oracle8i 8.16 or later :)-----------------------------
SELECT fieldName
FROM
(
SELECT fieldName,ROWNUM r
FROM
(
SELECT fieldName FROM myTable WHERE condition ORDER BY fieldName
)
)
WHERE r >= start_row AND r <= end_row;
FROM
(
SELECT fieldName, ROW_NUMBER() OVER (ORDER BY fieldName) r FROM myTable
)
WHERE r >= start_row AND r <= end_row; Note: require oracle8i 8.16 or later :)-----------------------------
SELECT fieldName
FROM
(
SELECT fieldName,ROWNUM r
FROM
(
SELECT fieldName FROM myTable WHERE condition ORDER BY fieldName
)
)
WHERE r >= start_row AND r <= end_row;
return pkg.myrctype
as
str varchar2(50);
begin
str:='select * from (select rownum rm,a.* from table_name a) where rm<='||p_n||' and rm>='||p_m;
open p_rc for str;
return p_rc;
end;
/
我有一种想法不知道是否可行。
select * from table_name
where rownum <= 一页中的行数
and rowid > (
select max(rowid) from table_name
where rownum <= ( 当前页码 -1) * 一页中的行数
);
where rownum <= ( 当前页码 -1) * 一页中的行数 rowid不一定按顺序先后排大小的,有可能最后那条记录的rowid比前面那条少。
你说的问题确实存在,但select rownum rm,a.* from table_name 出来的rownum也是按rowid排续的,不是吗?因此select rownum rm,a.* from table_name 也存在同样的问题,而且它还要搜索表中所有数据,降低了查询的速度。
如果需要按找某个列进行排序,则直接将rowid替换成排序的列即可,如果做排序的列有重复的话,你可以再加上rowid做定位不就解决了。
比如:
select * from table_name
where rownum <= 一页中的行数
and 排序的列 || rowid > (
select max(排序的列 || rowid) from table_name
where rownum <= ( 当前页码 -1) * 一页中的行数
);
当然,排序的列 || rowid 中可能要用到类型转换,比如:to_char(),rowidtochar()。
select * from table_name
where rownum <=40
minus
select * from table_name
where rownum <=50;
终结贴http://www.cnoug.org/bin/ut/topic_show.cgi?id=38&h=1&bpg=2&age=0