mysql 、pgsql、oracle 常见分页

//  总页数
int pages=0;
//  查询总行数的SQL
String rowSql= "select count(*) from "+ table_name;
......
//  判断页数,如果是页大小的整数倍就为rows/pageSize如果不是整数倍就为rows/pageSize+1
if (rows % pageSize == 0) {
   pages = rows / pageSize;
} else {
   pages = rows / pageSize + 1;
}

start :开始行数  currentPageNo:当前页码  pagesize:每页条数

1，mysql：  mysql  start 从0开始

select * from table_name  limit  start ,pagesize
-- 传递 currentPageNo（页码）、pageSize（每页条数）
select * from table limit (currentPageNo-1)*pageSize,pageSize;
--当数量到达万级以上，利用主键或者唯一索引进行数据分页，提高性能
select * from table where _id > (currentPageNo-1)*pageSize limit pageSize; 
-- 同上并加上条件对数据排序返回
select * from table where _id > (currentPageNo-1)*pageSize order by _id limit pageSize; 

2，pgsql ： start 从0开始

select * from table_name  limit  pageSize  offset  start;
-- 当start为0可以简写如下
select * from table_name limit pageSize

--排序
select * from table_name  order by xxxx  limit pageSize   offset start;

--加当前页码currentPageNo
select * from xxx limit pagesize offset （currentPageNo－1）*pagesize;

3，Oracle

--在Oracle中实现分页的方法大致分为两种，用ROWNUM关键字和用ROWID关键字
--ROWNUM
select * from (
    select rownum as row_no, t.* from  table_name  t  where xxx='xxx' and 
    rownum <= pageSize*currentPageNo  )
table_alias where table_alias.row_no > pageSize*(currentPageNo -1); 

--ROWID
SELECT *
  FROM (SELECT RID
          FROM (SELECT R.RID, ROWNUM LINENUM
                  FROM (SELECT ROWID RID
                          FROM TABLE1
                         WHERE TABLE1_ID = XX
                         ORDER BY order_date DESC) R
                 WHERE ROWNUM <= 20)
         WHERE LINENUM >= 10) T1,
       TABLE1 T2
 WHERE T1.RID = T2.ROWID;




 

--传入参数：页号 页大小
起始列:页号*页大小-页大小
截止列：页号*页大小+1

select * from (
　　select rownum rn,tt.* from (
　　　　select * from sys_task_status_his t where t.task_id=13 order by t.last_executetime desc
　　) tt where rownum<10 --截止列
)where rn>6 --起始列