select
c.rn rowno ,
t1.USERNAME USERNAME,
t1.PHONECODE PHONECODE,
t1.FIX_ADDRESS ADDRESS
from users t1,
(select b.rd ,b.rn
from (select a.rd, rownum rn
from (select rowid rd
from users
order by username ) a
where rownum <= $max) b
where b.rn > $min) c
where t1.rowid = c.rd
order by c.rn
----------------------------------------------------------------------------------
SELECT a.*
FROM a
WHERE ROWID IN (SELECT r_id FROM (select r_id,rownum rn from (select ROWID r_id from a order by i_index desc) where rownum <= inum*ipage )
where rn > inum*(ipage-1)
)
ORDER BY i_index desc;
--------------------------------------------------------------------------------
select t.*
from mytable t,
(select newrowid
from (select row_.*, rownum rownum_
from (select t.rowid newrowid from mytable t); row_
where rownum <= 20000010);
where rownum_ > 20000000); tt
where t.rowid = tt.newrowid
c.rn rowno ,
t1.USERNAME USERNAME,
t1.PHONECODE PHONECODE,
t1.FIX_ADDRESS ADDRESS
from users t1,
(select b.rd ,b.rn
from (select a.rd, rownum rn
from (select rowid rd
from users
order by username ) a
where rownum <= $max) b
where b.rn > $min) c
where t1.rowid = c.rd
order by c.rn
----------------------------------------------------------------------------------
SELECT a.*
FROM a
WHERE ROWID IN (SELECT r_id FROM (select r_id,rownum rn from (select ROWID r_id from a order by i_index desc) where rownum <= inum*ipage )
where rn > inum*(ipage-1)
)
ORDER BY i_index desc;
--------------------------------------------------------------------------------
select t.*
from mytable t,
(select newrowid
from (select row_.*, rownum rownum_
from (select t.rowid newrowid from mytable t); row_
where rownum <= 20000010);
where rownum_ > 20000000); tt
where t.rowid = tt.newrowid
from (select emp.*, dept.* rownum rownum_
from emp, dept
where emp.deptno = dept.deptno
and rownum <= 6)
where rownum_ >= 4 这样的简单好看。
楼主可以用pl/sql 或者 toad 的Explain Plan中看下sql的cost, 哪个cost小,效率就高......