四种流行的SQL分页语句

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();   
       }