oracle分页查询并按照某字段进行排序的数种解决方案

方案一（对于大数据集， 会明显的影响查询效率）：

SELECT *
FROM(
    SELECT ROWNUM RN,TA.*
    FROM(
        select t.id ID, o.name YYB,u.name XM, t.MC
        from tZDYSX t,tuser u,lborganization o 
        where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID
    )TA WHERE ROWNUM <= 20
)WHERE RN > 10

 

方案二（先选再排序， 不推荐使用）：

select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name

或写为如下形式（速度更快）

select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10

 

方案三(效率最高，推荐用此方案)：

select * from (select row_number() over (order by sign_time desc) as rownumber, id, signer_name, sign_time from b_sign_product)
where rownumber between 11 and 20