分页的相关概念:
1)当前页:即要显示或打开的那一页的页数
currPage
2)页面大小:即每页要显示数据的数量
如:每页都显示10条数据
pageSize
3)总数据:要显示的数据的总数,即要显示的结果集
totalSize
4)总页数:即显示总数据需要的页数
totalPage
它的计算公式为: (totalSize+pageSize-1)/pageSize
例如:
如要显示17条数据,每页显示5条,那么总页数: totalPage=(17+5-1)/5=4
5)计算当前页的第一条数据 计算公式:
(currPage-1)*pageSize+1
如要显示17 条数据,每页显示5条,那么第3页数据第一条数据是:
(3-1)*5+1=11
6)计算当前页的最后一条数据 计算公式:
(currPage-1)*pageSize+pageSize
如要显示17 条数据,每页显示5条,那么第3页数据最后一条数据是:
(3-1)*5+5=15
SQL SERVER 2005 实现分页的方式
一:存储过程方式
if exists(select * from sysobjects where name='pro_pageData')
drop procedure pro_pageData
go
create procedure pro_pageData
@pageNum int
as
select *
from
(
select *, row_number() over (order by stuid) as rowno
from student
) as s
where rowno>=(@pageNum-1)*5+1 and rowno <= (@pageNum -1)*5+5;
go
exec pro_pageData 4
go
二:一般语句方式(预编译)
select *
from
(
select *, row_number() over (order by stuid) as rowno
from student
) as s
where rowno>=(?-1)*5+1 and rowno <= (?-1)*5+5;
三:按条件查询后再对结果进行分页
select *
from
(
select *, row_number() over (order by stuid) as rowno
from student
where 1=1 and stuName like '%罗%'
) as s
where rowno>=(1-1)*5+1 and rowno <= (1 -1)*5+5;
注意:第一个where 用来匹配查询条件;
第二个where 用来显示特定页数据;
补充:分页数据的存储过程(完整版)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
go
use Db_TonyPaging
go
if exists ( select 1
from sysobjects
where id = object_id ( ' DepartDemo ' )
and type = ' U ' )
drop table DepartDemo
go
/* ============================================================== */
/* Table: DepartDemo */
/* ============================================================== */
create table DepartDemo (
PKID int identity ( 1 , 1 ),
DName nvarchar ( 200 ) null ,
DCode nvarchar ( 500 ) null ,
Manager nvarchar ( 50 ) null ,
ParentID int null default 0 ,
AddUser nvarchar ( 50 ) null ,
AddTime datetime null ,
ModUser nvarchar ( 50 ) null ,
ModTime datetime null ,
CurState smallint not null default 0 ,
Remark nvarchar ( 500 ) null ,
F1 int not null default 0 ,
F2 nvarchar ( 300 ) null ,
constraint PK_DEPARTDEMO primary key (PKID)
)
go
truncate table DepartDemo
go
/* **************创建54 条测试数据*********************
****************downmoo 3w@live.cn ************** */
declare @d datetime
set @d = getdate ()
declare @i int
set @i = 1
while @i <= 54
begin
-- 插入一条测试数据
insert into DepartDemo
select ' 国家统计局房产审计 ' + Cast ( @i as Nvarchar ( 10 )) + ' 科 ' , ' 0 ' , ' 胡不归 ' , 0 , ' DemoUser ' , getdate (),
'' , ' 1900-01-01 ' , 1 , ' 专业评估全国房价,为老百姓谋福祉 ' , 0 , ''
set @i = @i + 1
end
go
-- ***********分页存储过程用于SQL server2005/2008、2008R2****************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [ dbo ] . [ ZJF_CPP_GetPagedRecordFor2005_2008 ]
( @Table varchar ( 1000 ), -- 表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
@TIndex nvarchar ( 100 ), -- 主键,可以带表头 a.AID
@Column nvarchar ( 2000 ) = ' * ' , -- 读取字段
@Sql nvarchar ( 3000 ) = '' , -- Where条件
@PageIndex int = 1 , -- 开始页码
@PageSize int = 10 , -- 页大小
@Sort nvarchar ( 200 ) = '' -- 排序字段
)
AS
IF @Sql IS NOT NULL AND len ( ltrim ( rtrim ( @Sql ))) > 0
BEGIN
SET @strWhere = ' WHERE ' + @Sql + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END
if ( charindex ( ltrim ( rtrim ( @TIndex )), @Sort ) = 0 )
begin
if ( @Sort = '' )
set @Sort = @TIndex + ' DESC '
else
set @Sort = @Sort + ' , ' + @TIndex + ' DESC '
end
IF @PageIndex < 1
SET @PageIndex = 1
if @PageIndex = 1 -- 第一页提高性能
begin
set @strsql = ' select top ' + str ( @PageSize ) + ' ' + @Column
+ ' from ' + @Table + ' ' + @strWhere + ' ORDER BY ' + @Sort
end
else
begin
/* Execute dynamic query */
DECLARE @START_ID nvarchar ( 50 )
DECLARE @END_ID nvarchar ( 50 )
SET @START_ID = convert ( nvarchar ( 50 ),( @PageIndex - 1 ) * @PageSize + 1 )
SET @END_ID = convert ( nvarchar ( 50 ), @PageIndex * @PageSize )
set @strsql = ' SELECT ' + @Column + '
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @Sort + ' ) AS rownum,
' + @Column + '
FROM ' + @Table + ' WITH(NOLOCK) ' + @strWhere + ' ) AS D
WHERE rownum BETWEEN ' + @START_ID + ' AND ' + @END_ID + ' ORDER BY ' + @Sort
END
EXEC ( @strsql )
print @strsql
set @strsql = ' SELECT Count(1) as TotalRecords FROM ' + @Table + ' WITH(NOLOCK) ' + @strWhere
print @strsql
EXEC ( @strsql )