mysql

SELECT * FROM table LIMIT begin(省略即0)，pageSize
子查询优化深度分页
select * from orders_history where type=8 and
id>=(select id from orders_history where type=8 limit 100000,1)
limit 100;
优于下面
select * from orders_history where type=8 limit 100000,100;

oracle

对于没有order by语句的分页：

SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
FROM DONORINFO t
WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
AND ROWNUM <= page * size) table_alias
WHERE table_alias.rowno > (page-1)*size;

有order by语句的分页

SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
FROM(SELECT * FROM DONORINFO t
WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
AND TO_DATE ('20060731', 'yyyymmdd')
ORDER BY t.BIRTHDAY desc ) r
where ROWNUM <= page * size
) table_alias
WHERE table_alias.rowno > (page-1)*size;

sql执行顺序
from... where...group by ... having... select...rownum...order by
rownum 不能用> 因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。