SQL 分页查询-ROW_NUMBER() 函数

Sql Server 2000的自定义分页,但是在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函数

　　SQL Server2005为我们引入了一个ROW_NUMBER函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前，你不得不作棘手的事，像创建一个有序号列的临时表，然后把你的SELECT结果插入到这个临时表中。现在，用ROW_NUMBER函数，你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号，你只要简单的将ROW_NUMBER函数作为一列添加进你的SELECT语句中。你还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。

SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from 　　dbo.employee

结果

Row Number	employee_id	Firstname	Lastname	soc_sec
1	5623222	Tim	Jones	123-65-8745
2	5632111	Rob	Kinkad	456-69-8754
3	6365666	Jim	Miller	236-56-8989
4	7563333	Joe	Roberts	564-89-5555

　　这个查询返回所有的雇员和一个显示每条记录在哪一行的一个序号。OVER语句使SQL Server基于employee_id列增加行序号。换句话说，产生了行序号，就好像数据按employee_id做了排序。这是很重要的一点，因为你仍然可以改变SELECT的排序顺序。以下面的查询为例：

SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from 　　dbo.employee 　　ORDER BY soc_sec

结果

Row Number	employee_id	Firstname	Lastname	soc_sec
1	5623222	Tim	Jones	123-65-8745
3	6365666	Jim	Miller	236-56-8989
2	5632111	Rob	Kinkad	456-69-8754
4	7563333	Joe	Roberts	564-89-5555

　　注意第二个结果集数据是按社会安全编号来排序的，但是行号仍然创建得好像数据是按employee_id排序的。

-------------------------------分页存储过程

IF EXISTS (SELECT * FROM sysobjects where name='P_student')
DROP PROCEDURE P_student
go
CREATE PROCEDURE P_student
@startIndex INT,
    @pageSize INT
AS
begin WITH studentList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.stuid ) Row,
O.stuid,O.stuname,O.stuage,O.stuinfo
from student O)

SELECT Row, stuid,stuname,stuage,stuinfo
FROM studentList
WHERE Row between @startIndex and @startIndex+@pageSize-1
end

-------------------分页2---------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Deer_Page]
(
@startIndex INT
,@pageSize INT
,@strSql varchar(5000) ---查询条件
,@TableName varchar(50)
,@DoCount AS bit=1     -- 0值返回记录总数, 非 0 值则返回记录
)
AS
begin tran
IF @DoCount=0
    Goto GetCount
Else
    Goto GetSearch

GetCount: --返回记录总数
    DECLARE @SearchSql AS Nvarchar(4000)
    SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE IntReserve1=0'
    exec sp_executesql @SearchSql
    --print @SearchSql
COMMIT TRAN
    return

GetSearch: --返回记录
DECLARE @SqlQuery varchar(4000)
SET @SqlQuery='SELECT * FROM
           (SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row, * from '+@TableName+' O Where IntReserve1=0) as temp
            WHERE Row BETWEEN '+cast(@startIndex as varchar) +' and '+cast(@startIndex+@pageSize-1 as varchar)+ @strsql
---print @SqlQuery
execute(@SqlQuery)
COMMIT TRAN