ROW_NUMBER函数在分页中的使用示例

在SQL Server2005中有一个ROW_NUMBER函数，它将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。
以北风数据库为例，代码如下：  
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS ROW,ProductName FROM Products

运行的结果如下：
ROW ProductName
--------------------- ----------------------------------------
1 Chai
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
6 Grandma's Boysenberry Spread
如果要取出表中2-5个产品名称，把上面的查询变为子查询，并在主查询的WHERE子句过滤查询的结果，上面的子查询当作一个独立的表，以在主查询使用（在子查询后面的AS关键字，是用来给这个虚拟“表”指定一个新建的名称）
SELECT ROW,ProductName
FROM(SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS ROW,ProductName FROM Products)
AS ProductsWithRowNumbers WHERE Row >=2 AND Row<=5

运行的结果如下：
ROW ProductName
--------------------- ----------------------------------------
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
使用表变量
如果需要对取回的数据集进行进一步操作，就可能需要保存它，保存在一个TABEL变量中，在一个存储过程中表变量常用来存放临时数据。TABLE表变量可以作为正常表来使用。
以北风数据库为例，代码如下：  
CREATE PROCEDURE MyTable
AS
DECLARE @MyProducts table
(MyROW int,
MyProductID int,
MyProductName varchar(40))
BEGIN
INSERT INTO @MyProducts
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS ROW,ProductID,ProductName FROM Products
SELECT MyROW,MyProductName FROM @MyProducts WHERE MyROW >=2 AND MyROW<=5
END
GO

运行的结果如下：
MyROW MyProductName
----------- ----------------------------------------
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
使用临时表
如果需要对取回的数据集进行进一步操作，就可能需要保存它，保存在临时表中，在一个存储过程中临时表常用来存放临时数据。临时表可以作为正常表来使用。
以北风数据库为例，代码如下：
CREATE PROCEDURE [dbo].[MyTable]
AS
CREATE TABLE #MyProducts
(MyROW int,
MyProductID int,
MyProductName varchar(40))
BEGIN
INSERT INTO #MyProducts
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS ROW,ProductID,ProductName FROM Products
SELECT MyROW,MyProductName FROM #MyProducts WHERE MyROW >=2 AND MyROW<=5
END

运行的结果如下：
MyROW MyProductName
----------- ----------------------------------------
2 Chang
3 Aniseed Syrup
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix