Oracle之分页查询

Oracle之分页查询

分页查询

在Oracle中实现分页的方法大致分为两种，用ROWNUM关键字和用ROWID关键字

1.ROWNUM

无排序

select *
  from (select A.*, ROWNUM as rn from table A where ROWNUM <=50) B
 where b.rn > 40;

排序

select *
  from (select A.*, ROWNUM as rn
          from (select * from loan_apply order by systime desc) A
         where rownum <= 60) B
 where b.rn > 50;

分组后查询做分页

ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段)

select *
  from (select A.*,
               ROW_NUMBER() OVER(partition by tradecode order by systime desc) rn
          from loan_apply A) B
 where B.rn <= 50)

ROWID

SELECT *
  FROM (SELECT RID
          FROM (SELECT R.RID, ROWNUM rn
                  FROM (SELECT ROWID RID FROM table ORDER BY systime DESC) R
                 WHERE ROWNUM <= 20)
         WHERE rn >= 10) T1,
       table T2
 WHERE T1.RID = T2.ROWID;