四种流行的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
select top @pagesize * from tablename where id not in (select top @pagesize*(@page-1) id from tablename order by id) order by id
select * from tablename limit position, counter
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(); }