SQL2012新特性一次一个数据块----特殊的查询分页

     对于数据库人员来说，在软件项目开发的过程中，或多或少的不可避免的就是经常遇到类似“UI查询结果设计”这样的说法，当听到UI设计的时候，多数人的第一反应是“靠！关我毛事！~这是其他人的问题！”。

     然而，UI的设计确实存在一个难点，必须由数据库人员不得不去进行解决的难点：分页。

     无论最终的查询结果显示在何处，用户界面的可用空间对于显示在屏幕上的无线多的结果都显得过小（尤其是在基于WEB的应用程序中）。

     UI设计师目前采用的是分页作为通用的解决方案。解决方案允许先显示排名靠前的10个结果，并允许用户点击按钮[MORE]来获取下一次显示的10个结果！~在多数的情况下，没有任何UI设计师希望能够缓存整个数据集（数据集中可能包含上百万条数据）。

     自从在SQL2005种引入排名函数TOP后，人们经常使用这个函数来进行分页。但是实现这种解决方案所需要的SQL语句有一些复杂。在SQL2012中，专门设计了一种新的方法来解决这种复杂性：OFFSET...FETCH。

     

OFFSET： 向SELECT查询表示跳过多少行！

FETCH：  表示从特定的位置开始检索检索多少行！不是截取   

 

   以下代码为例，WEB设计人员请求你协助完成客户持有金钱排名。在其中简单的形式中，该界面只是每页显示10个人员。 

    基础数据准备   

USE MyDB; --当前数据库
IF EXISTS (Select * From sys.objects Where name =N'Custommers' And Type In ('S','U')) --表存在即删除
 DROP TABLE Custommers   
ELSE
  CREATE TABLE Custommers --买家
  (
  Id INT PRIMARY KEY IDENTITY,--主键ID
  CustomName NVARCHAR(10),   --买家名称
  HadMoney MONEY             --买家所持有的金钱
  )
   /*以上代码执行完成需要刷新下IntelliSence缓存，更新下当前智能提示
    *键盘快捷键 Ctrl+Shift+R。
    *定义变量i作为循环数据，并进行写入基础测试数据
    *@author 系统管理员-咔咔 
    *@time 2013-11-22
    */
 DECLARE @i INT; 
 SET @i=1;
 WHILE(@i<100)
 BEGIN
    INSERT INTO dbo.Custommers
            ( CustomName, HadMoney )
    VALUES  ( N'张三'+CAST(@i AS VARCHAR(5)), -- CustomName - nvarchar(10)
              @i  -- HadMoney - money
              );
    SET @i=@i+1;
END

   使用下列的SELECT TOP（10）查询可以很方便的检索出第一页的数据

 

SELECT TOP 10  CustomName,HadMoney
FROM dbo.Custommers
ORDER BY HadMoney

 

   获得如下结果　　

 

CustomName HadMoney
---------- ---------------------
张三1 1.00
张三2 2.00
张三3 3.00
张三4 4.00
张三5 5.00
张三6 6.00
张三7 7.00
张三8 8.00
张三9 9.00
张三10 10.00

(10 行受影响)

 

 

   真正的难点在于如何获取接下来的第二页的10条数据、第三页、第四页.....可以使用OFFSET.....Fetch完成，以下是代码示例　

 

SELECT CustomName,HadMoney
FROM dbo.Custommers
ORDER BY HadMoney
offset 10 rows
fetch next 10 Rows Only

 

　结果如下

CustomName HadMoney　　　／／注意这里的查询结果是进行检索
---------- ---------------------
张三11       11.00
张三12       12.00
张三13       13.00
张三14       14.00
张三15       15.00
张三16       16.00
张三17       17.00
张三18       18.00
张三19       19.00
张三20       20.00

(10 行受影响)

CustomName HadMoney　　　／／注意这里的查询结果是进行检索
---------- ---------------------
张三11       11.00
张三12       12.00
张三13       13.00
张三14       14.00
张三15       15.00
张三16       16.00
张三17       17.00
张三18       18.00
张三19       19.00
张三20       20.00

(10 行受影响)

 

　关于这个用法的有些的限制需要注意：　

• 使用OFFSET...FETCH必须进行ORDER BY
• OFFSET可以单独进行使用，但不能单独使用FETCH
• 与SELECT TOP 不能一起使用
• 可以使用算数或变量来表示跳过多少行或获取多少，但是不可以使用标量子查询

 

 

　以上代码运行请在ＳＱＬ２０１２的１８０天试用的非ＥＸＰＲＥＳＳ版本中运行！