sqlServer、oracle、MySQL分页语句

分页需要的五个属性

• 记录总数
• 页面大小（每页现实的记录数量）
• 总页数
• 当前页
• 数据结果集

为了数据传输的方便我们将这些数据封装成一个类：

package student.entity;

import java.util.List;

public class Page {
//当前页
	private int currentPage;
//页面大小
	private int pageSize;
//总数据量
	private int totalCount;
//总页数
	private int totalPage;
//	当前页的数据集合
	private List<Student> students;
	
	public Page() {

	}
	//页面总数自动计算
	public Page(int currentPage, int pageSize, int totalCount, List<Student> students) {
		this.currentPage = currentPage;
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		this.students = students;
		this.totalPage= this.totalCount%this.pageSize==0?this.totalCount/this.pageSize:this.totalCount/this.pageSize+1;
	}
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	public int getPageSize() {
		return pageSize;
	}
	/**
	 * 当设置了总数据量和页面大小以后，自动算出总页数
	 * @param pageSize
	 */
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
		if(this.totalCount!=0) {   //自动计算总页数
			this.totalPage= this.totalCount%this.pageSize==0?this.totalCount/this.pageSize:this.totalCount/this.pageSize+1;
		}
	}
	public int getTotalCount() {
		return totalCount;
	}
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
		if(this.pageSize!=0) {   //自动计算总页数
			this.totalPage= this.totalCount%this.pageSize==0?this.totalCount/this.pageSize:this.totalCount/this.pageSize+1;
		}
	}
	public int getTotalPage() {
		return totalPage;
	}
	
	public List<Student> getStudents() {
		return students;
	}
	public void setStudents(List<Student> students) {
		this.students = students;
	}
	
}

分页的规律

假设我们每页需要显示10条记录则每页显示的记录数与页数的关系如下：

第n页	此页显示的第一条记录对应总记录数	此页最后一条记录对应总记录数
1	1	10
2	11	20
···	···	···
n	(n-1)*10+1	n*10

MySQL分页

mysql中记录是从第0条开始记录的，所以规律有点不同。

第n页	此页显示的第一条记录对应总记录数	此页最后一条记录对应总记录数
0	0	9
1	10	19
2	20	29
···	···	···
n	n*10	(n+1)*10-1

Mysq分页可以使用limit语句

select * from Student limit currentPage*pageSize,pageSize;

SQL Server 分页

SQL Server 2003

使用关键字top

select top pageSize * from Student where id not in 
(select top (currentPage-1)*pageSize id from Student order by Sno asc )

SQL Server 2005以后

可以使用row_number() 函数
关于该函数用法，可参考该博客：https://blog.csdn.net/qq_27139403/article/details/82856202

with tab as
(select ROW_NUMBER() over(order by  Sno ASC) as r,* from Student) 
 select * from tab as t where r between pageSize*(currentPage-1)+1 and pageSize*currentPage"

SQL Server 2012以后

可使用 offset fetch next only
类似于mysql的limit

select * from Student order by Sno offset (currentPage-1)*pageSize+1 rows fetch next pageSize rows only;

oracle

oracle中记录与SQLServer一样从1开始
可使用rownum(rownum 只能用< 要选出>的数据，只能在重新将r作为一个属性装入一张新的表中）

select * from
(
select rownum r, t.* from
(select s.* from Student s order by s.Sno asc) t
where r <=currentPage*pageSize
)
where r>=(currentPage-1)*pageSize+1;