SQL分页

取出sql表中第31到40的记录（以自动增长ID为主键）

sql server方案1：

        selecttop 10 * from t where id not in (select top 30 id from t order by id ) orde by id

sql server方案2：

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

 

mysql方案：select* from t order by id limit 30,10

 

oracle方案：select * from (select rownum r,* from t wherer<=40) where r>30

sql语句分页，不同的数据库下的分页方案各不一样，下面是主流的三种数据库的分页sql：

sql server:

              String sql =

              "select top " + pageSize+ " * from students where id not in" +

 

 "(select top " + pageSize *(pageNumber-1) + " id from students order by id)" +

 

 "order by id";

 

mysql:

 

              String sql =

              "select * from students orderby id limit " + pageSize*(pageNumber-1) + "," + pageSize;

             

oracle:

 

              String sql =

               "select * from " + 

               (select *,rownum rid from (select * fromstudents order by postime desc) where rid<=" + pagesize*pagenumber +") as t" +

               "where t>" +pageSize*(pageNumber-1);