利用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