支持任意字段排序及多表关联查询的存储过程分页

 本文作者：草上飞
网址： www.newbooks.com.cn
QQ：1469886
如需转载，请保留以上信息。
谢谢！
点击下载支持多表关联任意字段排序的存储过程分页SQL>>>
网上有很多现成的存储过程分页sql，但是大多数只支持主键字段或者唯一值字段进行排序。而对于有重复值的字段进行排序的时候，数据会遗漏。 
而且很多存储过程分页脚本也不支持多表关联查询的分页，而我们现实应用中，一般都是多表关联的查询，针对这几个问题，我网上随便找了一个 
现成的脚本，进行了一下修改。修改后的脚本支持多表查询和任意字段排序（包括数值型字段）。 
以下脚本注释以我开发的新书城网上书店（www.newbooks.com.cn）为例进行注释。 
本存储过程分页的主要思路是，排序的同时引入主键字段，如果排序字段值重复的时候，来利用排序字段值相等，但是主键不相等的条件来取记录，具体以price字段升序排序为例进行讲解： 
1.先取得(@PageIndex-1)*@PageSize条记录中的price字段和主键字段。这些记录的顺序按照price升序和主键升序。 
相关sql伪代码为：select top (@PageIndex-1)*@PageSize tblbooks.price,tblbooks.bookid from tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid  
2.从上面的记录中取得top 1记录，top 1记录按照price降序和主键降序。其实就是取得上面记录中的最后一条记录，将这条记录的price字段和主键字段的值赋值给2个临时变量。 
相关sql伪代码为: 
select top 1 @orderFldValue=@orderFldName,@keyFldValue=@fldName from (select top (@PageIndex-1)*@PageSize tblbooks.price,tblbooks.bookid from tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid) 
3.我们取得上面2个值后，就可以把这2个值作为条件，来取得我们的分页数据了。思路就是top 20 ... where price>取出来的price or (price=取出来的price and 主键bookid>取出来的主键值) 
相关sql伪代码为： 
select top 20 @listFldName from @tblName where @strWhere and (price>@orderFldValue or (price=@orderFldValue and bookid>@keyFldValue)) 
从而我们取出了分页数据。 
调用的代码如下： 
exec GetRecordFromPage "tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid","tblbooks.bookid","tblbooks.bookid,tblbooks.bookname,tblbooks.price", 
"tblbooks.price","",20,3,0," tblbooktypes.typecode like '0.1.20%'" 
本存储过程sql语句如下：

/**/ /*  
  参数说明: @tblName     需要查询的表名。如图书表tblbooks  。如果是多表（图书表关联图书分类表）则写成：tblbooks inner join tblbooktypes on tblbooks.bookid=tblbooktypes.bookid   
           @fldName      主键字段名 bookid   
       @listFldName  需要查询的字段。如：书名(tblbooks.bookname)、作者(tblbooks.author)、价格(tblbooks.price)  
       @orderFldName 需要排序的字段。 如(tblbooks.price)  我们以非主键且有重复字段价格字段进行排序  
       @orderFldType 需要排序的字段的类型。   因为price这段类型为float，所以我们这里设置值为"float"。  
           @PageSize     每页记录数    
           @PageIndex    要获取的页码  
           @OrderType    排序类型, 0 - 升序, 1 - 降序  
           @strWhere     查询条件 (注意: 不要加 where)  
作者：草上飞  
Q Q:1469886  
说明：本存储过程为在优化新书城网上书店（www.newbooks.com.cn）的分页时候整理出来的。本存储过程可以任意转载，但在转载过程中请保留以上信息。谢谢！  
新书城网上书店（www.newbooks.com.cn）现有30几万的图书数量，利用该存储过程达到了根据价格、销量、出版日期等字段快速排序的效果，具体分页速度可进入该网站进行查看。  
如对该存储过程有疑问，请与本人联系。  
*/   


CREATE     PROCEDURE  GetRecordFromPage  
     @tblName        varchar ( 500 ),        
     @fldName        varchar ( 50 ),         
     @listFldName        varchar ( 255 ),         
     @orderFldName        varchar ( 50 ),         
     @orderFldType        varchar ( 50 ),         
     @PageSize       int   =   10 ,             
     @PageIndex      int   =   1 ,              
     @OrderType      bit   =   0 ,              
     @strWhere       varchar ( 2000 )  =   ''    
AS   

declare   @strSQL     nvarchar ( 4000 )        --  主语句  
declare   @strTmp     varchar ( 1000 )        --  临时变量  
declare   @strOrder   varchar ( 500 )         --  排序类型  
declare   @strOrder2   varchar ( 500 )        --    
declare   @orderFldValue   nvarchar ( 100 )   -- 排序字段对应的值    
declare   @keyFldValue   nvarchar ( 100 )   -- 主键字段对应的值 add  
declare   @operator   char ( 1 )  -- add by caoy  
declare   @tempValueSql   varchar ( 100 )  
declare   @strOrderby   varchar ( 5 )  
if  ( @orderFldType = ' float ' )  
     set   @tempValueSql = ' cast(@orderFldValue as float) '   
else   
     set   @tempValueSql = ' @orderFldValue '   

-- 获取表明 。  
declare   @tablename   varchar ( 20 )  
if   charindex ( ' . ' , @orderFldName ) > 1    
     set   @tablename = left ( @orderFldName , charindex ( ' . ' , @orderFldName ) - 1 )  
else   
     set   @tablename = @orderFldName   
if   @OrderType   !=   0     
begin   
     set   @operator = ' < '   
     set   @strOrderby = '  desc '   
     set   @strOrder2 = '  asc '   
end   
else   
begin   
     set   @operator = ' > '   
     set   @strOrderby = '  asc '   
     set   @strOrder2 = '  desc '   
end   
set   @strOrder = '  order by  ' +   @orderFldName + @strOrderby   
if   @fldName != @orderFldName    -- 如果排序字段不是主键字段，则增加主键排序  
     set   @strOrder = @strOrder + ' , ' + @fldName + @strOrderby   
-- 先得到orderFldValue和keyValue   
set   @strSQL = ' select top 1 @orderFldValue=convert(nvarchar(100), ' + @orderFldName + ' ,20) '    /**/ /***注意，如果需要排序的字段的值长度超过Nvarchar(100)，请修改此处***********/   
if   @fldName != @orderFldName     
     set   @strSQL = @strSQL + ' ,@keyFldValue= ' + @fldName   
else   
     set   @strSQL = @strSQL + ' ,@keyFldValue=1 '   
set   @strSQL = @strSQL + '  from (select top  '   +   str (( @PageIndex - 1 ) * @PageSize )  +   '   '   
     +   @orderFldName    
if   @fldName != @orderFldName    -- add by caoy  
     set   @strSQL = @strSQL + ' , ' + @fldName   
set   @strSQL = @strSQL +   '  from  '   +   @tblName   +   ''    
if   @strWhere   !=   ''   
     set   @strSQL = @strSQL +   '  where  ' + @strWhere   
set   @strSQL = @strSQL +   @strOrder   +   ' ) as  ' + @tablename + '  order by  '   +   @orderFldName   + @strOrder2   
if   @fldName != @orderFldName    -- add by caoy  
     set   @strSQL = @strSQL + ' , '   +   @fldName   + @strOrder2   
-- print @strSQL  
exec    sp_executesql  @strSQL ,N ' @orderFldValue nvarchar(100) output,@keyFldValue nvarchar(100) output ' , @orderFldValue  output, @keyFldValue  output     /**/ /***注意，如果需要排序的字段的值长度超过Nvarchar(100)，请修改此处***********/     
-- 得到排序字段值和主键值结束  



if   @PageIndex   =   1   
begin   
     set   @strTmp   =   ''   
     if   @strWhere   !=   ''   
         set   @strTmp   =   '  where ( '   +   @strWhere   +   ' ) '   

     set   @strSQL   =   ' select top  '   +   str ( @PageSize )  +   '   ' +   @listFldName + '  from  '   
         +   @tblName   +   ''   +   @strTmp   +   '   '   +   @strOrder   
     exec  ( @strSQL )  
end   
else   
begin    
     -- 取得top数据并返回  
     set   @strSQL   =  N ' select top  '   +   str ( @PageSize )  + '   '   +    @listFldName + '  from  '   
         +   @tblName   +   '  where ( ' + @orderFldName + @operator + @tempValueSql + '  and @keyFldValue=@keyFldValue '   
     if   @fldName != @orderFldName    -- add by caoy  
         set   @strSQL = @strSQL +   '  or ( ' + @orderFldName + ' = ' + @tempValueSql + '  and  ' + @fldName + @operator + ' @keyFldValue)) and (1=1 '   
      
     if   @strWhere   !=   ''   
         set   @strSQL = @strSQL + '  and  '   +   @strWhere   
     set   @strSQL = @strSQL +   ' ) ' + @strOrder   
         if   @fldName = @orderFldName            
         set   @keyFldValue = 1   
     -- print @strSQL  
     exec  sp_executesql  @strSQL ,N ' @orderFldValue nvarchar(100),@keyFldValue nvarchar(100) ' , @orderFldValue , @keyFldValue   

      
end   

SET  QUOTED_IDENTIFIER  OFF   
GO