oracle利用rownum和rowid分页时order by问题
程序员文章站
2022-04-04 13:05:57
...
利用rownum分页可以参考下面的sql
select * from (select a.*, rownum rn from (select * from tb_test where name = 'XXX' order by state asc, adddate desc , rownum asc ) a where rownum <= 20 ) where rn > 0
order by 后面的rownum asc(或desc) 是必须的 否则会出现意想不到的结果。
利用rowid分页可以参考下面的sql,rowid的效率比rownum高
第一种:
select * from tb_test where rowid in ( --这里选择要查询出的字段 select rid from ( select rownum rn,rid from ( select rowid rid from tb_test WHERE name = 'XXX' ORDER BY state ASC, adddate DESC --这里进行表排序 ) where rownum <= 20 --这里是上限 ) where rn > 0 --这里是下限 ) ORDER BY state ASC, adddate DESC --这里再次排序
第二种:
select * from (select rid from (select a.rid, rownum rn from (select rowid rid from tb_test where name = 'XXX' order by state asc, adddate desc) a where rownum <= 20) where rn > 0) t1, tb_test t2 where t1.rid = t2.rowid order by state asc, adddate desc
使用rowid进行分页 在最里层 和 最外层都必须使用order by排序,此时rownum asc(或desc)不是必须的了。
参考:http://www.jianbage.com/basic/database/567.html
http://www.jianbage.com/basic/database/565.html