Oracle分页

Oracle 经典分页

SELECT t2.rn, t2.col1, t2.col2, t2.col3, t2.col4
  FROM (SELECT ROWNUM rn, t1.col1, t1.col2, t1.col3, t1.col4
           FROM (SELECT u.col1, u.col2, t.col3, b.col4
                    FROM t_table1 u, t_table2 t, t_table3 b
                   WHERE t.id = u.tid
                     AND t.bid = b.id
                   ORDER BY t.startDate DESC) t1
          WHERE ROWNUM <= 19) t2
 WHERE t2.rn >= 10;

 

如果经常需要翻到很大的页码，或者要查出很多字段时，下面的写法会比上面的写法稍好，其实它们的原理是相同的，只是先只查出各表各行数据的 rowid，不获取字段的实际值，在嵌套查询中避免传递大量的数据。

 

SELECT t3.rn, t4.col1, t4.col2, t5.col3, t6.col4
  FROM (SELECT t2.urid, t2.trid, t2.brid, t2.rn
           FROM (SELECT ROWNUM rn, t1.urid, t1.trid, t1.brid
                    FROM (SELECT u.rowid urid, t.rowid trid, b.rowid brid
                             FROM t_table1 u, t_table2 t, t_table3 b
                            WHERE t.id = u.tid
                              AND t.bid = b.id
                            ORDER BY t.startDate DESC) t1
                   WHERE ROWNUM <= 19) t2
          WHERE t2.rn >= 10) t3, t_table1 t4, t_table2 t5, t_table3 t6
 WHERE t3.urid = t4.rowid AND t3.trid = t5.rowid AND t3.brid = t6.rowid
 ORDER BY t3.rn ASC;