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 用来显示特定页数据;
补充:分页数据的存储过程(完整版)
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 )