数据库中的分页问题

随着数据库中存储的数据的增多，满足用户查询条件的数据也随之增加。而用户一般不可能一次性看完所有的数据， 很多时候也不需要看完所有数据。在这种情况下，分页返回用户查询的数据就显得相当的重要。分页返回用户数据有如下好处：
1、减少服务器磁盘系统地读取压力
2、减少网络流量，减轻网络压力
3、减轻客户端显示数据的压力
4、提高处理效率。
      一般而言，分页处理分为两种：应用程序中的分页处理和数据库中的分页处理。目前大多数的应用都是在应用程序中借助支持数据分页处理的数据库访问组件（如DataGrid控件）实现分页处理。实际上，在数据库中实现分页处理，可以从源头减少数据处理量，效果往往可能跟明显。本文主要讨论数据库的分页问题。
      常规的取第n页数据方法为： Select top PageSize * from TableA where Primary_Key not in (select top (n-1)*PageSize Primary_Key from TableA )。
      对于应用程序而言，所做的就是在生成分页处理的T-SQL语句前先计算好各数字， 对于数据库而言，应该采用动态的T-SQL语句。
 以下是使用上述原理实现的通用分页处理存储过程：

create  proc up_PageView
(
     @tableName sysname,
     @colKey     nvarchar( 100),
     @pageCurrent  int  =  1,
     @pageSize  int  =  10,
     @colShow  nvarchar( 4000)  =  '',
     @colOrder  nvarchar( 200)  =  '',
     @where  nvarchar( 2000)  =  '',
     @pageCount  int output
)
as
begin
     if  object_id( @tableName)  is  null
     begin
     raiserror( ' the table is not existing! ',  16, 1)
     return 
     end
     if   isnull( @colShow,  '')  =  ''
         set  @colShow   =  ' * '
     if  isnull( @colOrder, '')  =  ''
         set  @colOrder  =  ''
     else 
         set  @colOrder  =  ' order by  '  +  @colOrder
     if  isnull( @where,  '')  =  ''
         set  @where  =  ''
     else
         set  @where  =  ' where  ' +  @where
     declare  @sql  nvarchar( 4000)
     if  @pageCount  is  null
         begin
         set  @sql  =  ' select @pageCount = count(*) from  '  +  @tableName  +  '   '  +  @where
         Exec sp_executesql  @sql,  ' @pageCount int output ',  @pageCount output
         set  @pageCount  = ( @pageCount  +  @pageSize  - 1) / @pageSize
         end
     if  @pageCurrent  =  1
         set  @sql  =  ' select top '  + '   '  +  convert( nvarchar( 10),  @pageSize)  +  '   '
                 +  @colshow  +  '   '  +  ' from  '  +  @tableName  +  '   '  +  @where  +  '   '  +  @colOrder 
     else
         begin
         set  @sql  =  ' select top '  + '   '  +  convert( nvarchar( 10),  @pageSize)  +  '   '
                 +  @colshow  +  '   '  +  ' from  '  +  @tableName  +  '   '  +  @where 
         set  @sql  =  @sql  +  '   '  +  ' and  ' +  @colKey  + '  not in (  ' 
                 +  ' select top '  + '   '  +  convert( nvarchar( 10), ( @pageCurrent  -  1)  *  @pageSize)  +  '   '
                 +  @colKey  +  '   '  +  ' from  '  +  @tableName  +  '   '  +  @where   +  '  ) '
         set  @sql  =  @sql  +  '   '  +  @colOrder
         end
     -- execute the dynamic query
     exec ( @sql)
end

         这种方法的缺点是为了排除该页以前的页， 必须使用top n取大量的数据并缓存起来，在关联元表查询出最终结果，这样做的效率比较低。通常情况下， 我们都是对单主健（使用单个字段定位纪录）的表进行分页查询。因此，如果能使用一个字符串变量纪录指定页的所有主健，在使用in子句配合纪录的指定页主健就可以查询出最终的结果来。下面是改进的存储过程：

  create  proc up_PageView
(
     @tableName sysname,
     @colKey     nvarchar( 100),
     @pageCurrent  int  =  1,
     @pageSize  int  =  10,
     @colShow  nvarchar( 4000)  =  '',
     @colOrder  nvarchar( 200)  =  '',
     @where  nvarchar( 2000)  =  '',
     @pageCount  int output
)
as
begin
     if  object_id( @tableName)  is  null
     begin
     raiserror( ' the table is not existing! ',  16, 1)
     return 
     end
     if   isnull( @colShow,  '')  =  ''
         set  @colShow   =  ' * '
     if  isnull( @colOrder, '')  =  ''
         set  @colOrder  =  ''
     else 
         set  @colOrder  =  ' order by  '  +  @colOrder
     if  isnull( @where,  '')  =  ''
         set  @where  =  ''
     else
         set  @where  =  ' where  ' +  @where
     declare  @sql  nvarchar( 4000)
     if  @pageCount  is  null
         begin
         set  @sql  =  ' select @pageCount = count(*) from  '  +  @tableName  +  '   '  +  @where
         Exec sp_executesql  @sql,  ' @pageCount int output ',  @pageCount output
         set  @pageCount  = ( @pageCount  +  @pageSize  - 1) / @pageSize  --  get total pages
         end
     if  @pageCurrent  =  1
         begin
         set  @sql  = N ' select top '  +N '   '  +  convert( nvarchar( 10),  @pageSize)  + N '   '
                 +  @colshow  + N '   '  + N ' from  '  +  @tableName  + N '   '  +  @where  + N '   '  +  @colOrder
         exec ( @sql)
         end
     else
         begin
         declare  @topN  int,  @topN1  int
--         set @topN = @pageSize
--         set @topN1 = @pageCurrent * @pageSize
         set  @pageCurrent  =  @pageCurrent  *  @pageSize
         set  @sql  = N ' select @n = @n - 1, @s = case when @n <  '  +  convert( nvarchar( 10),  @pageSize)  +
                    N '  then @s +  '' , ''  + quotename(@colKey, N '''''''' )  '  + N ' else  '''''  + N '  end  ' 
                     + N '  from  ' +  @tableName  + N '   '  +  @where
         -- make query effect only @pageCurrent records
         -- Query only top @pageCurrent * @pageSize
         set  rowcount  @pageCurrent 
         exec sp_executesql  @sql,  ' @n int, @s nvarchar(max) output ',  @pageCurrent,  @sql output
         set  rowcount  0  -- recover to default config
         set  @sql  =  stuff( @sql,  1,  1, N '')  --  remove the first ','
         -- exec the query
         Exec (N ' select  '  +  @colShow  + N '   '  +  ' from '  + N '   '  +  @tableName  + N '   '  
                 + N ' where  '  +  @colKey  + N '  in ( '  +  @sql  +  ' ) '  +  @colOrder)
         end
end
go

         另外， sql server 2005 增加了一些新的功能如取得排名或顺序的函数（Rank(), Dense_Rank(), Row_Number()）, 利用这些新的功能也能进行分页处理，下面以sql server 2005 自带的数据库AdventureWorks为例结合Row_Number() 实现分页处理：
  

create  proc up_GetPagen
(
     @pageSize  int, 
     @pageCurrent  int
)
as 
begin
     select  *  from 
    ( select ROW_NUMBER()  over( ORDER  BY productid) RowNum,  *  from production.product )OrderData
     where RowNum  between ( @pageCurrent  -  1) * @pageSize  +  1  and  @pageCurrent  *  @pageSize
     order  by productid
end

      不尽如此，这种新功能有许多妙用， 如可以取班级排名前N名或第n名到第m名的学生等等，这类问题将会在我以后的文章中进行讨论！
这是我的第一篇博客， 呵呵，处来砸到，讨论的问题希望对大家有所帮助，另外，如有疑难或错误之处，敬请指出！
   select * from (
select ROW_NUMBER() over(order by singerID )as'orderID',* from singer)
as a 
where a.orderID between 1 and 2