关于数据简单分页的实现

本文用到的两个 sql的技术点：

（1）Row_Number()函数；

（2）公共表表达式(CTE:Common Table Expression)；

 

示例数据来源：

为Northwind示例数据库下的orders表，customers表，employees表；

Northwind示例数据库下载地址：http://www.microsoft.com/en-us/download/details.aspx?id=23654

 

示例代码：

  
 --INPUT:
 -- @pageIndex:
 -- @pageNum:
 --OUTPUT:
 --返回两个查询
 --查询1：表示满足用户指定需求的页的记录
 --查询2：返回当前要查询内容的最新页数，以及分页的大小
 
 declare @pageIndex int --用户选择的页
 declare @pageNum int --页面大小
 
 declare @latestpagecount int
 declare @befpageindex int

 declare @startRecPos int
 declare @endRecPos int
 
 set @pageIndex=7
 set @pageNum=20
 
 set @befpageindex= @pageIndex-1
 if(@befpageindex<0)
 begin
 set @befpageindex=0
 end
 
 set @startRecPos=@befpageindex *@pageNum
 set @endRecPos=@pageIndex*@pageNum
 
 --因为我这个查询的主表是orders，所以这里计算页数是用的orders表
 select @latestpagecount=(COUNT(*)/@pageNum +1) from Orders
 
 ;with sales_order_cte
 as
 (
 select
 --ROW_NUMBER() over(partition by b.EmployeeID order by b.EmployeeID ) as 'row number',
 ROW_NUMBER() over(order by b.EmployeeID ) as 'rownum',
 b.EmployeeID,
 b.FirstName,
 b.LastName,
 a.OrderID,
 a.OrderDate,
 a.RequiredDate,
 c.CompanyName,
 c.ContactName,
 c.ContactTitle
 from Orders a
  left join Employees b
  on a.EmployeeID =b.EmployeeID
  left join Customers c on a.CustomerID =c.CustomerID
 )
 
 --返回用户指定页的记录
 select *
 from sales_order_cte
 where rownum>@startRecPos and rownum<=@endRecPos
 
 --返回最新的总页数，及分页大小
 select @latestpagecount as 'pagecount',@pageNum as 'pagesize'
 

相关参考文档：

（1）Row_Number()函数

原文地址：http://msdn.microsoft.com/zh-cn/library/ms186734(v=SQL.90).aspx

ROW_NUMBER (Transact-SQL)

           

 

语法

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

参数

< partition_by_clause>

将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。有关 PARTITION BY 的语法，请参阅OVER 子句 (Transact-SQL)。

< order_by_clause>

确定将 ROW_NUMBER 值分配给分区中的行的顺序。有关详细信息，请参阅 ORDER BY 子句 (Transact-SQL)。当在排名函数中使用 <order_by_clause> 时，不能用整数表示列。

返回类型

bigint      

备注

ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

示例

注意：
OVER 子句中的 ORDER BY 将对 ROW_NUMBER 进行排序。如果将ORDER BY 子句添加到以非 'Row Number' the 的某列（或多列）作为排序依据的 SELECT 语句，则结果集将以外部的 ORDER BY 为依据进行排序。

以下示例将根据年初至今的销售额，返回 AdventureWorks 中销售人员的 ROW_NUMBER。

复制

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

以下示例将返回行号为 50 到 60 的行（包含这两行），并按 OrderDate 进行排序。

复制

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

以下示例说明了如何使用 PARTITION BY 参数。

复制

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER 
    (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

 

（2）公共表表达式(CTE:Common Table Expression)

原文地址：http://msdn.microsoft.com/zh-cn/library/ms175972.aspx

WITH common_table_expression (Transact-SQL)

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

Transact-SQL 语法约定

语法

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

参数

expression_name

公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression> 子句中定义的任何其他公用表表达式的名称不同，但expression_name 可以与基表或基视图的名称相同。在查询中对expression_name 的任何引用都会使用公用表表达式，而不使用基对象。

column_name

在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与CTE_query_definition 结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

CTE_query_definition

指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求。有关详细信息，请参阅“备注”部分和CREATE VIEW (Transact-SQL)。

如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。

注释

创建和使用公用表表达式的准则

下面的准则适用于非递归公用表表达式。有关适用于递归公用表表达式的准则，请参阅后面的“定义和使用递归公用表表达式的准则”。

• CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。

• 可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。

• CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

• 不允许在一个 CTE 中指定多个 WITH 子句。例如，如果CTE_query_definition 包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。

• 不能在 CTE_query_definition 中使用以下子句：

  • ORDER BY（除非指定了 TOP 子句）

  • INTO

  • 带有查询提示的 OPTION 子句

  • FOR XML

  • FOR BROWSE

• 如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。

• 可以使用引用 CTE 的查询来定义游标。

• 可以在 CTE 中引用远程服务器中的表。

• 在执行 CTE 时，任何引用 CTE 的提示都可能与该 CTE 访问其基础表时发现的其他提示相冲突，这种冲突与引用查询中的视图的提示所发生的冲突相同。发生这种情况时，查询将返回错误。

定义和使用递归公用表表达式的准则

下面的准则适用于定义递归公用表表达式：

• 递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。

• 定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。

• 定位点成员和递归成员中的列数必须一致。

• 递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。

• 递归成员的 FROM 子句只能引用一次 CTE expression_name。

• 在递归成员的 CTE_query_definition 中不允许出现下列项：

  • SELECT DISTINCT

  • GROUP BY

  • PIVOT（当数据库兼容级别为 110 时。请参阅SQL Server 2012 中数据库引擎功能的重大更改。）

  • HAVING

  • 标量聚合

  • TOP

  • LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）

  • 子查询

  • 应用于对 CTE_query_definition 中的 CTE 的递归引用的提示。

下面的准则适用于使用递归公用表表达式：

• 无论参与的 SELECT 语句返回的列的为 Null 性如何，递归 CTE 返回的全部列都可以为空。

• 如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围的默认值为 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。

• 不能使用包含递归公用表表达式的视图来更新数据。

• 可以使用 CTE 在查询上定义游标。CTE 是定义游标结果集的select_statement 参数。递归 CTE 只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。

• 可以在 CTE 中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。如果为 CTE 查询，Index Spool/Lazy Spool 则显示在查询计划中，并具有额外的 WITH STACK 谓词。这是一种确认正确递归的方法。

• CTE 递归部分中的分析和聚合函数适用于当前递归级别的集合而不适用于 CTE 集合。ROW_NUMBER 之类的函数仅对当前递归级别传递给它们的数据子集执行运算，而不对传递给 CTE 的递归部分的整个数据集合执行运算。有关详细信息，请参阅J. Using analytical functions in a recursive CTE。

示例

A.创建一个简单公用表表达式

以下示例显示 Adventure Works Cycles 的每个销售代表每年的销售订单总数。

Transact-SQL

复制

USE AdventureWorks2012;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO