SQL Server 2005 Row_Number() 祥解

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就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页.

 

==========================================================================

关于ROW_NUMBER()函数

2008-01-06 10:20

微软最新发布的MSSQL2005,对TSQL进行了小规模的加强，有些函数的确非常实用。就比如ROW_NUMBER()函数，实现了原先一直要用存储过程来进行大数据分页的功能。现在有了这个函数，只要简单一个语句就能解决任何情况下的数据分页，不管你要排序的字段是否唯一。 　　个人感觉，这个函数应该能再进的一步优化性能，当然这个工作不是我们所能为，MS不去开发，我们就没办法了。 select * from (SELECT ROW_NUMBER() OVER (order by id desc) as pos from [table] where year(birth)=1981) as sp where pos between 1 and 5 　　实际底层的工作逻辑不是很清楚，假如是每次执行都需要进行全表排序的话，那真的是太浪费资源了。即使有了索引，不需要再进行全表排序，是否必然会进行一次ROW_NUMBER()函数的全表运算呢？ 　　我们假设索引都有一个索引表的rowID，就类似于ROW_NUMBER()产生的值。那么事先已经有过索引的，是不是就能直接根据索引表的rowID进行高效分页呢？这种方法应该是可行，具体MSSQL2005中是否有这个功能，还需要进一步学习。 　　按照以上方法，应该能简单写出分页的SQL语句： SELECT from [table] where year(birth)=1981 and ROW_NUMBER() between 1 and 5 order by id desc 　　分析器执行此语句时，对于ROW_NUMBER()函数，首先检查是否有索引匹配，若匹配，则直接访问索引中的rowID进行条件读取，若是无匹配索引，则根据当前排序条件重新进行一次全表的ROW_NUMBER()计算。 不过这个方法可能会增加索引的工作强度，由于每次的索引重建都需要进行一次全表ROW_NUMBER()运算。不过由于索引是多字段及多方向的，例如，一个索引有2个字段，那么就必然需要4个ROW_NUMBER()表，如果索引字段更多，就变得很复杂了。因此，可以选择性的为某些字段的排序方向保存rowID。 　　下面是我简单写的一个存储过程，希望对你有用，相对之前的存储分页，性能自然也是非常高效的。个人觉得用存储过程调用，不管是性能上还是使用上，都应该更方便一些。如果你觉得直接程序中用SQL语句调用，那也是完全没问题的。 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[ShowPage] @tblName   varchar(255),       -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @strOrder varchar(255)='',      -- 排序的字段名 @PageSize   int = 10,          -- 页尺寸 @PageIndex int = 1,           -- 页码 @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL   varchar(5000) if @strWhere !='' set @strWhere=' where '+@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和条数即可。

优快云上还有个存储过程实现分页的代码：

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