四种流行的SQL分页语句

发牌SO

1.Oracle:

select * from ( select row_.*, rownum rownum_ from ( query_SQL ) row_ where rownum < =max) where rownum_ >= min
select * from (select e.*,rownum rn from (select * from emp) e) a1 where a1.rn > min and a1.rn  < max;  
select * from (select a1.*,rownum rn from(select * from emp) a1 where rownum<=max) where rn>=min;  
select * from (select e.*,rownum rn from emp e) a1 where a1.rn between min and max


2.SQL Server:

select top @pagesize * from tablename where id not in (select top @pagesize*(@page-1) id from tablename order by id) order by id

  3.MySQL

select * from tablename limit position, counter

4.DB2

select * from (select *,rownumber() as ROW_NEXT from tablename) where ROW_NEXT between min and max



/**
* Oracle 分页sql语句生成方法
* @param sql sql语句
* @param pageIndex 页下标
* @param pageSize 页面大小
* @return
*/
public  static String createOraclePagingSql(String sql, int pageIndex, int pageSize){
int m = (pageIndex-1) * pageSize;  
int n = m + pageSize;  
StringBuffer pagingSelect = new StringBuffer(100);  
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ")
.append(sql)  
.append(" ) row_ where rownum <=")
.append(n)
.append(") where rownum_ >")
.append(n);      
return pagingSelect.toString();   
}