CREATE PROC sp_PageView @tbname sysname, --要分页显示的表名 @FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序 @Where nvarchar(1000)='', --查询条件 @PageCount int OUTPUT --总页数 AS DECLARE @sql nvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@tbname) IS NULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@tbname) RETURN END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) RETURN END--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END--其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@Where,N'')=N'' SET @Where=N'' ELSE SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN SET @sql=N'SELECT @PageCount=COUNT(*)' +N' FROM '+@tbname +N' '+@Where EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT SET @PageCount=(@PageCount+@PageSize-1)/@PageSize END--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' '+@Where +N' '+@FieldOrder) ELSE BEGIN SELECT @PageCurrent=@TopN1, @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname +N' '+@Where +N' '+@FieldOrder SET ROWCOUNT @PageCurrent EXEC sp_executesql @sql, N'@n int,@s nvarchar(4000) OUTPUT', @PageCurrent,@sql OUTPUT SET ROWCOUNT 0 IF @sql=N'' EXEC(N'SELECT TOP 0' +N' '+@FieldShow +N' FROM '+@tbname) ELSE BEGIN SET @sql=STUFF(@sql,1,1,N'') --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' WHERE '+@FieldKey +N' IN('+@sql +N') '+@FieldOrder) END END
CREATE PROC sp_PageView @sql ntext, --要执行的sql语句 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小 @PageCount int OUTPUT --总页数 AS SET NOCOUNT ON DECLARE @p1 int --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@sql, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT--计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount SET @PageCurrent=1 ELSE SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标 EXEC sp_cursorclose @p1
--要分页的原始数据 CREATE TABLE tb( ID int PRIMARY KEY, --记录编号 grade varchar(10), --类别名称 uptime datetime) --更新时间 INSERT tb SELECT 1 ,'a','2004-12-11' UNION ALL SELECT 2 ,'b','2004-12-11' UNION ALL SELECT 3 ,'c','2004-12-11' UNION ALL SELECT 4 ,'a','2004-12-12' UNION ALL SELECT 5 ,'c','2004-12-13' UNION ALL SELECT 6 ,'c','2004-12-13' UNION ALL SELECT 7 ,'a','2004-12-14' UNION ALL SELECT 8 ,'a','2004-12-15' UNION ALL SELECT 9 ,'b','2004-12-16' UNION ALL SELECT 10,'b','2004-12-17' UNION ALL SELECT 11,'a','2004-12-17'--分页定义表 CREATE TABLE tb_Page( grade varchar(10) PRIMARY KEY, --类别名称,与tb表的grade关联 Records int, --每页显示的记录数 Orders int) --在页中的显示顺序 INSERT tb_Page SELECT 'c',2,1 UNION ALL SELECT 'b',1,2 UNION ALL SELECT 'a',2,3 GO--实现分页处理的存储过程 CREATE PROC p_PageView @PageCurrent int=1 --要显示的当前页码 AS SET NOCOUNT ON --得到每页的记录数 DECLARE @PageSize int SELECT @PageSize=SUM(Records) FROM tb_Page IF ISNULL(@PageSize,0)<0 RETURN--分页显示处理 SET @PageCurrent=@PageCurrent*@PageSize SET ROWCOUNT @PageCurrent SELECT SID=IDENTITY(int,1,1),ID INTO # FROM( SELECT TOP 100 PERCENT a.ID FROM tb a LEFT JOIN tb_Page b ON a.grade=b.grade ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END,--分类没有定义的显示在最后 ((SELECT COUNT(*) FROM tb WHERE grade=a.grade AND (uptime>a.uptime OR uptime=a.uptime AND id>=a.id))-1) /b.Records, b.Orders,a.ID DESC)a IF @PageCurrent>@PageSize BEGIN SET @PageCurrent=@PageCurrent-@PageSize SET ROWCOUNT @PageCurrent DELETE FROM # END SELECT a.* FROM tb a,# b WHERE a.ID=b.ID ORDER BY b.SID GO--调用 EXEC p_PageView 2 /*--结果 ID grade uptime ----------- ---------- ------------------------------------------------------ 3 c 2004-12-11 00:00:00.000 9 b 2004-12-16 00:00:00.000 7 a 2004-12-14 00:00:00.000 4 a 2004-12-12 00:00:00.000 2 b 2004-12-11 00:00:00.000 --*/
如果是asp.net 用建议下一个瞻远分页演示V2.0控件很好用的。
---sql2005 Select * FROM ( select ROW_NUMBER() Over(order by 排序字段 desc) as rowId,* from 数据表 ) where rowId between 页面大小*(页号-1) and 页面大小*页号
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize --第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1
CREATE TABLE tb(
ID int PRIMARY KEY, --记录编号
grade varchar(10), --类别名称
uptime datetime) --更新时间
INSERT tb SELECT 1 ,'a','2004-12-11'
UNION ALL SELECT 2 ,'b','2004-12-11'
UNION ALL SELECT 3 ,'c','2004-12-11'
UNION ALL SELECT 4 ,'a','2004-12-12'
UNION ALL SELECT 5 ,'c','2004-12-13'
UNION ALL SELECT 6 ,'c','2004-12-13'
UNION ALL SELECT 7 ,'a','2004-12-14'
UNION ALL SELECT 8 ,'a','2004-12-15'
UNION ALL SELECT 9 ,'b','2004-12-16'
UNION ALL SELECT 10,'b','2004-12-17'
UNION ALL SELECT 11,'a','2004-12-17'--分页定义表
CREATE TABLE tb_Page(
grade varchar(10) PRIMARY KEY, --类别名称,与tb表的grade关联
Records int, --每页显示的记录数
Orders int) --在页中的显示顺序
INSERT tb_Page SELECT 'c',2,1
UNION ALL SELECT 'b',1,2
UNION ALL SELECT 'a',2,3
GO--实现分页处理的存储过程
CREATE PROC p_PageView
@PageCurrent int=1 --要显示的当前页码
AS
SET NOCOUNT ON
--得到每页的记录数
DECLARE @PageSize int
SELECT @PageSize=SUM(Records) FROM tb_Page
IF ISNULL(@PageSize,0)<0 RETURN--分页显示处理
SET @PageCurrent=@PageCurrent*@PageSize
SET ROWCOUNT @PageCurrent
SELECT SID=IDENTITY(int,1,1),ID
INTO # FROM(
SELECT TOP 100 PERCENT a.ID
FROM tb a
LEFT JOIN tb_Page b ON a.grade=b.grade
ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END,--分类没有定义的显示在最后
((SELECT COUNT(*) FROM tb
WHERE grade=a.grade
AND (uptime>a.uptime OR uptime=a.uptime AND id>=a.id))-1)
/b.Records,
b.Orders,a.ID DESC)a
IF @PageCurrent>@PageSize
BEGIN
SET @PageCurrent=@PageCurrent-@PageSize
SET ROWCOUNT @PageCurrent
DELETE FROM #
END
SELECT a.* FROM tb a,# b
WHERE a.ID=b.ID
ORDER BY b.SID
GO--调用
EXEC p_PageView 2
/*--结果
ID grade uptime
----------- ---------- ------------------------------------------------------
3 c 2004-12-11 00:00:00.000
9 b 2004-12-16 00:00:00.000
7 a 2004-12-14 00:00:00.000
4 a 2004-12-12 00:00:00.000
2 b 2004-12-11 00:00:00.000
--*/
用建议下一个瞻远分页演示V2.0控件很好用的。
---sql2005
Select * FROM (
select ROW_NUMBER() Over(order by 排序字段 desc) as rowId,* from 数据表
)
where rowId between 页面大小*(页号-1) and 页面大小*页号