1、MySQL用limit分页
/*
* sql:可以是单表的查询语句,也可以是多表的联合查询语句
* firstIndex:其实的索引
* pageSize:每页显示的记录数
*/
select o.* from (sql) o limit firstIndex,pageSize
eg: select * from (select * from Student) limit 0,20; //取1-20条数据
select * from (select * from Student) limit 20,20; //取21-40条数据
第二种 写法
select * from Student where limit (currentPage-1)*pageSize ,pageSize;
2、oracle用ROWNUM分页
/*
* firstIndex:起始索引
* pageSize:每页显示的数量
* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
*/
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex
eg: select * from(select a.*,ROWNUM rn from(select * from Student order by id asc) a where ROWNUM<=20) where rn>0 // 取1-20条数据
select * from(select a.*,ROWNUM rn from(select * from Student order by id asc) a where ROWNUM<=(20+20)) where rn>20 // 取21-40条数据
第二种写法
select * from ( select rownum rn,t.* from Student t where rownum <= currentPage*pageSize ) where rownum > (currentPage -1)*pageSize;
3、SqlServer用top分页
/*
* firstIndex:起始索引
* pageSize:每页显示的数量
* orderColumn:排序的字段名
* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
*/
select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(sql)) as o where rownumber>firstIndex;
eg: select top 20 o.* from (select row_number() over(order by id asc) as rownumber,* from(select * from Student)) as o where rownumber>0; // 取1-20条数据
第二种写法
select top pageSize * from Student where id not in (select top (currentPage-1)*pageSize id from Student) ;