数据库分页语句

1、MySQL

select * from tb_user order by id asc limit 10 offset 10;

[size=small][color=blue]limit指定每页大小，offset指定偏移量，在程序代码中可以写成这样：
select * from tb_user order by id asc limit pageSize offset (pageNo-1)*pageSize;
[/color][/size]
[color=blue]也可以简写成下面语句，前面的参数指定偏移量，后面的参数指定每页大小[/color]

select * from tb_user order by id asc limit 10,10; 

2、SQL Server

select top 10 * from Orders where OrderId not in(select top 10 OrderID from Orders order by OrderId asc) order by OrderId asc

[size=small][color=blue]在程序中可以写成这样：
select top pageSize * from Orders where OrderId not in(select top (pageNo-1)*pageSize OrderId from Orders order by OrderId asc) order by OrderId asc;[/color][/size]

3、Oracle 10g

select temp.* from (select rownum num,emp.* from emp emp where rownum<15) temp where temp.num>=6 and temp.num<=10;

[size=small][color=blue]rownum为虚拟字段，动态生成。
select temp.* from (select rownum num,emp.* from emp emp where rownum<=pageSize*pageNo) temp where temp.num>(pageNo-1)*pageSize;
[/color][/size]