三种主流数据库的分页语句:
SQL SERVER:
String sql = "select top " + pageSize + "* from XXX where id not in " + "(select top " + pageSize * (pageNumber-1) + "id from XXX order by id)" + "order by id";
String sql = "select top " + pageSize + "* from XXX where id in " + "(select top " + pageSize * pageNumber + "id from XXX order by id desc)" + "order by id desc";
pageSize:每页要显示的数量(行)
pageNumber:页码
例子1:(取数据库中21-30条的记录,)
select top 10 from student where id not in("select top 10*(3-1) id from student order by id) order by id;
步骤:
1、("select top 10*(3-1) id from student order by id) 先取出数据中前20条记录,并按id排序;
2、id not in("select top 10*(3-1) id from student order by id) ,id不为前20条记录的数据即第21条到后面所有的记录;
3、第21条到第N条的记录的前10条(select top 10),即第21-30记录
例子2:(取数据库中21-30条的记录,)
select top 10 from student where id in("select top 10*3 id from student order by id desc) order by id desc;
步骤:
1、("select top 10*3 id from student order by id desc) 先取出数据中前20条记录,并按id倒序排列;
2、id in("select top 10*3 id from student order by id desc) ,数据记录为30,29,28……;
3、取前10条记录(select top 10 ),即30,29 ……21,然后再倒序排列order by id desc。
MYSQL:
String sql = "select * from XXX order by id limit " + pageSize * (pageNumber - 1) + "," + pageSize;
例子:(取数据库中21-30条的记录,)
select * from student order by id limit 20,10;
MYSQL中特有的函数limit,可以方便地取出某段记录。
ORACLE:
String sql = "select * from " + (select * ,rownum rid from (select * from XXX order by id desc) where rid<=" + pageSize*pageNumber + ") as t" + "where t>" + pageSize*(pageNumber -1);
例子3:
一种分页方法
<%
int i=1;
int numPages=14;
String pages = request.getParameter("page") ;
int currentPage = 1;
currentPage=(pages==null)?(1):{Integer.parseInt(pages)}
sql = "select count(*) from tables";
ResultSet rs = DBLink.executeQuery(sql) ;
while(rs.next()) i = rs.getInt(1) ;
int intPageCount=1;
intPageCount=(i%numPages==0)?(i/numPages):(i/numPages+1);
int nextPage ;
int upPage;
nextPage = currentPage+1;
if (nextPage>=intPageCount) nextPage=intPageCount;
upPage = currentPage-1;
if (upPage<=1) upPage=1;
rs.close();
sql="select * from tables";
rs=DBLink.executeQuery(sql);
i=0;
while((i<numPages*(currentPage-1))&&rs.next()){i++;}
%>
//输出内容
//输出翻页连接
合计:<%=currentPage%>/<%=intPageCount%><a href="List.jsp?page=1">第一页</a>
<a href="List.jsp?page=<%=upPage%>">上一页</a>
<%
for(int j=1;j<=intPageCount;j++){
if(currentPage!=j){
%>
<a href="list.jsp?page=<%=j%>">[<%=j%>]</a>
<%
}else{
out.println(j);
}
}
%>
<a href="List.jsp?page=<%=nextPage%>">下一页</a><a href="List.jsp?page=<%=intPageCount%>">最后页 </a>