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

 本文作者：草上飞
网址：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