四种流行的SQL分页语句

[color=darkred]1.Oracle:[/color]

 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

[color=darkred] 2.SQL Server:[/color]

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

[color=darkred] 3.MySQL[/color]

  select * from tablename limit position, counter

[color=darkred] 4.DB2[/color]

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