sql server分页

最新推荐文章于 2024-09-11 17:17:07 发布

转载最新推荐文章于 2024-09-11 17:17:07 发布 · 71 阅读

本文介绍了使用SQL进行分页查询的几种方法，包括基于ROW_NUMBER()的分页、利用子查询实现灵活的数据检索以及计算每页显示记录数的Java实现。通过这些技巧，可以有效地提高数据库查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select top 10 id,name,displayName from PM_User where name like '%yang%' order by id

with tempPagination as(select ROW_NUMBER() OVER(ORDER BY  id) as RowNumber, 
id,name,displayName from PM_User where name like '%yang%' ) 
select * from tempPagination where RowNumber>10  and RowNumber<=20

select * from 
(
    select ROW_NUMBER()over(order by id desc) as rowNo,* from  PM_User where name like '%yang%'
) as t
where rowNo between 0 and 200


 select * from 
    (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from PM_User) as temp
where rowNumber between 0 and 39


SELECT COUNT(*) FROM (
        select id,name,displayName from PM_User where name like '%yang%'
) a

public class Page {
	public static void main(String[] args) {
		int[] p = paging(38, 380, 0);
		System.out.println(p[0]);
		System.out.println(p[1]);
		
		p = paging(38, 380, 1);
		System.out.println(p[0]);
		System.out.println(p[1]);
		
		p = paging(38, 380, 2);
		System.out.println(p[0]);
		System.out.println(p[1]);
		
		p = paging(38, 380, 9);
		System.out.println(p[0]);
		System.out.println(p[1]);
		
		p = paging(38, 380, 10);
		System.out.println(p[0]);
		System.out.println(p[1]);
		
		p = paging(38, 380, 11);
		System.out.println(p[0]);
		System.out.println(p[1]);
	}
	//rowCountPerPage //每页多少条数据
	//totalRows   //总条数
	//pageCount  //总页数
	//page  当前页码
	//pageCount;//总页数
	public static int[] paging(int rowCountPerPage, int totalRows, int page){
		if (rowCountPerPage > 0) {//每页多少条数据
			int pageCount=0;
			if (page < 1) page = 1;
			// Compute the begin and end row number //计算开始和结束行号
			if (totalRows > rowCountPerPage) {
				pageCount = (int) (totalRows + rowCountPerPage - 1) / rowCountPerPage;//总页数
			} else if (totalRows <= rowCountPerPage) {
				pageCount = 1;
			}
			if (page > pageCount) {
				page = pageCount;
			}
			int iBegin = (page - 1) * rowCountPerPage + 1;//当前页开始index
			int iEnd = iBegin + rowCountPerPage - 1;//当前页结束index
			return new int[]{iBegin, iEnd};
		}
		return new int[]{0,0};
	}
}