SQL分页

SELECT *
FROM   (SELECT a.*,
               rownum rn
        FROM   (SELECT *
                FROM   oc a
                ORDER  BY k001) a
        WHERE  ROWNUM <= 20)
WHERE  rn >= 10 AND rn < 20;
--此处之所以在外面包了一层，是因为 ROWNUM不能像rn一样加条件，只有用里面 SQL检索出的结果集才能分页


SELECT b.*
FROM   (SELECT *
        FROM   (SELECT a.*,
                       rownum rn
                FROM   (SELECT ROWID rid,
                               status
                        FROM   product a
                        WHERE  company_id = ?
                        ORDER  BY status) a
                WHERE  rownum <= 20)
        WHERE  rn > 10 ) a,
       product b
WHERE  a.rid = b.rowid;
--用rowid的方式分页效率比单纯用 rownum的效率要高