设为首页 - 加入收藏 焦点技术网
热搜:java
当前位置:首页 >

各种数据库的分页查询语句

2014-11-06 11:34:00.0 数据库应用  
导读:转自:http://www.2cto.com/database/201311/255641.html1.oracle数据库分页select * from (select a.*,rownum rc from 表名 where rownum<=endrow) a where a.rc>=startrow 2.DB2数据库分页Select * from (select rownumber() over...。。。

转自:http://www.2cto.com/database/201311/255641.html


1.oracle数据库分页

select * from (select a.*,rownum rc from 表名 where rownum<=endrow) a where a.rc>=startrow
 
2.DB2数据库分页
Select * from (select rownumber() over() as rc,a.* from (select * from 表名 order by 列名) as a) where rc between startrow and endrow
 
3.SQL Server 2000数据库分页
Select top pagesize * from 表名 where 列名 not  in(select top pagesize*page 列名 from  表名 order by 列名) order by 列名
 
4.SQL Server 2005数据库分页
Select * from (select 列名,row_搜索number() over(order by  列名1) as 别名from 表名) as t where t.列名1>=startrow and t.列名1<=endrow
 
5.MySQL数据库分页
Select * from 表名 limit startrow,pagesize
(Pagesize为每页显示的记录条数)
 
6.PostgreSQL数据库分页
Select * from 表名 limit pagesize,offset startrow
(Pagesize为每页显示的记录条数.)
 
 7.通用模式select * from (
 select * from tb_student where sid not in(
     select sid from tb_student where rownum<=(currentPage-1)*pageSize)

) where rownum <=pageSize;


项目实战:


  • Oracle 

   StringBuffer tmpBuffer = new StringBuffer();   tmpBuffer.append("SELECT * FROM (SELECT originTable.*, ROWNUM ");   tmpBuffer.append(ROWNUMFIELD);   tmpBuffer.append(" FROM (");   tmpBuffer.append(originalSql);   tmpBuffer.append(")");   tmpBuffer.append(" originTable ");      //syp 屏蔽,解决翻页数据错误的问题   /*if (iEnd <= totalRows) {    tmpBuffer.append(" WHERE ROWNUM<");    tmpBuffer.append(Integer.toString(iEnd));   }*/   tmpBuffer.append(") WHERE ");   tmpBuffer.append(ROWNUMFIELD);   tmpBuffer.append(">=");   tmpBuffer.append(Integer.toString(iBegin));   if (iEnd <= totalRows) {    tmpBuffer.append(" and ");    tmpBuffer.append(ROWNUMFIELD);    tmpBuffer.append("<");    tmpBuffer.append(Integer.toString(iEnd));   }   originalSql = tmpBuffer.toString();

  • Mysql
 /**  * 实现mysql的分页查询的语句  * @param originalSql  * @param rowCountPerPage  * @param totalRows  * @param page  * @return  */ public static String parsePageMysqlSQL(String originalSql, int rowCountPerPage,int totalRows, int page) {  int iBegin = 0;  int pageCount = 0;  if (rowCountPerPage > 0 && totalRows > rowCountPerPage) {   if (totalRows % rowCountPerPage == 0)    pageCount = totalRows / rowCountPerPage;   else    pageCount = totalRows / rowCountPerPage + 1;   if (page < 1)    page = 1;   if (page > pageCount) {    page = pageCount;   }   iBegin = (page - 1) * rowCountPerPage ;   StringBuffer tmpBuffer = new StringBuffer();   tmpBuffer.append("SELECT originTable.* ");   tmpBuffer.append(" FROM (");   tmpBuffer.append(originalSql);   tmpBuffer.append(")");   tmpBuffer.append(" originTable ");   tmpBuffer.append("limit ");   tmpBuffer.append(Integer.toString(iBegin));   tmpBuffer.append(",");   tmpBuffer.append(rowCountPerPage);   originalSql = tmpBuffer.toString();  }  return originalSql; }


(编辑: langjian2012)

网友评论
相关文章