数据库分页处理(转)

出处：http://www.cnblogs.com/riasky/p/3361044.html
sql server:

sql server solution1:

select top10 * from table where id not in(select top 30 id from t order by id) order by id

sql server solution2:

select top 10 * from table where id in(select top 40 id from t order by id) order by id desc

String sql = "select top"+ pageSize+"* from t where id not in "+" select top " + pageSize*(pageNumber-1)+" id from t order by id)"+"order by id";

 

mysql :

select * form t order by id limit 30,10

String sql="select * from t order by id limit " + pageSize*(pageNumber-1)+","+pageSize;

 

oracle:

select * from (select rownum r,* from t where r<=40) where r>30)

String sql = "select * from "+(select *,rownum rid from (select * from t order by time desc) where rid<="+pageSize*pageNumber+") as tt "+"where tt>"pageSize*(pageNumber)-1);