Sql Server 2000的自定义分页,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如ID要递增).现在Sql Server 2005中提供了一个函数ROW_NUMBER(),可以使自定义分页变得简单许多.
我们先来看看ROW_NUMBER()是干什么的.执行下面这段SQL语句:
SELECT [ReportID],[UserName], [ReportID],
[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM [ExecutionLog]
很简单,ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID).现在,你看到了自定义分页的影子了吗?:)下面,我们看看怎么具体应用这个RowNo进行分页.
现在,假设我每一页的数据是10条,我们就可以使用如下所示的SQL语句返回指定页的数据:
@"
SELECT TOP 10 *
FROM
(
SELECT top 10 [InstanceName], [UserName], [ReportID],
[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM [ExecutionLog]
) AS A
WHERE RowNo > " + pageIndex*10
pageIndex就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页.
==========================================================================
微软最新发布的MSSQL2005,对TSQL进行了小规模的加强,有些函数的确非常实用。就比如ROW_NUMBER()函数,实现了原先一直要用存储过程来进行大数据分页的功能。现在有了这个函数,只要简单一个语句就能解决任何情况下的数据分页,不管你要排序的字段是否唯一。
set ANSI_NULLS ON CREATE PROCEDURE [dbo].[ShowPage] declare @strSQL varchar(5000) if @strWhere !='' set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS pos,'+@strGetFields+' FROM ['+@tblName+']'+@strWhere+') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize) exec (@strSQL) |
===========================================================================
DECLARE@pagenumASINT,@pagesizeASINT
SET@pagenum=2
SET@pagesize=3
SELECT*
FROM(SELECTROW_NUMBER()OVER(ORDERBYnewsidDESC)ASROWnum,
newsid,topic,ntime,hits
FROMnews)ASD
WHEREROWnumBETWEEN(@pagenum-1)*@pagesize+1AND@pagenum*@pagesize
ORDERBYnewsidDESC
aspx里面只需给SQL传入pageid和条数即可。
CSDN上还有个存储过程实现分页的代码:
ALTERPROCEDUREnews_Showlist
(
@tblName varchar(255), --表名
@strGetFieldsvarchar(1000), --需要返回的列
@fldNamevarchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@strWhere varchar(1500), --查询条件(注意:不要加where)
@Sortvarchar(255) --排序的方法
)
AS
declare@strSQL varchar(5000) --主语句
declare@strTmp varchar(110) --临时变量
declare@strOrdervarchar(400) --排序类型
if@Sort='desc'
begin
set@strTmp='<(selectmin'
set@strOrder='orderby'+@fldName+'desc'
--如果@OrderType不是,就执行降序,这句很重要!
end
else
begin
set@strTmp='>(selectmax'
set@strOrder='orderby'+@fldName+'asc'
end
if@PageIndex=1
begin
if@strWhere!=''
begin
set@strSQL='selecttop'+str(@PageSize)+''+@strGetFields+' from'+@tblName+'where'+@strWhere+''+@strOrder
end
else
begin
set@strSQL='selecttop'+str(@PageSize)+''+@strGetFields+' from'+@tblName+''+@strOrder
end
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set@strSQL='selecttop'+str(@PageSize)+''+@strGetFields+' from'
+@tblName+'where'+@fldName+''+@strTmp+'('+@fldName+')from(selecttop'+str((@PageIndex-1)*@PageSize)+''+@fldName+'from'+@tblName+''+@strOrder+')astblTmp)'+@strOrder
if@strWhere!=''
set@strSQL='selecttop'+str(@PageSize)+''+@strGetFields+' from'
+@tblName+'where'+@fldName+''+@strTmp+'('
+@fldName+')from(selecttop'+str((@PageIndex-1)*@PageSize)+''
+@fldName+'from'+@tblName+'where'+@strWhere+''
+@strOrder+')astblTmp)and'+@strWhere+''+@strOrder
end
exec(@strSQL)
RETURN