不同数据库的分页实现

2/13/2017来源:SQL技巧人气:1093

Oracle         1: 伪列 rownum          select * from       (SELECT t.*,rownum rn FROM t_user t where         rownum<page.getCurrentPage()*page.getPageSize ()        ) where rn>(page.getCurrentPage()-1)*page.getPageSize()      2: 物理列 rowId          select * FROM t_user t where rowId in(                   select rowId from                    (select t.*,rownum rn FROM t_user t)                     where rn<page.getCurrentPage()*page.getPageSize ()                     and rn>(page.getCurrentPage()-1)*page.getPageSize()                                 ) MySQL SELECT * FROM t_user t limit n,m;           n 代表的开始位置  记录的第一条是0开始           m 代表的返回的记录条数  jdbc分页:   SELECT * FROM t_user t limit               (page.getCurrentPage()-1)*page.getPageSize (),                     page.getPageSize () Sql server         select top n from t_user ;       表示显示表中的前  n条记录               select top (page.getCurrentPage()-1)*page.getPageSize ()          from           ( select top page.getCurrentPage()*page.getPageSize ()           from t_user  order by id desc) DB2分页查询 SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20 以上表示提取第10到20的纪录 select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=endIndex )    where rowid > startIndex