多表联合多字段排序的通用存储过程，请各位大侠雅正

zlhran 2008-12-23 04:08:53 


/*------------------------------------------------------------

--

--通用分页存储过程（二分法+临时表修正排序）

--zlhran@gmail.com

--

-------------------------------------------------------------*/

CREATE PROCEDURE [Pr_PageList_finnal]

(

@tblName     nvarchar(200),        ----要显示的表或多个表的连接

@fldName     nvarchar(500) = '*',  ----要显示的字段列表

@pageSize    int = 10,        	   ----每页显示的记录个数

@page        int = 10,             ----要显示那一页的记录

@sortRule varchar(1000),  ---排序约束 类似  a.xx desc,b.xx as

@sortRuleReverse varchar(1000), ---排序约束 与上一条件相反即可如 a.xx asc,b.xx desc

@filter      nvarchar(1000) = null,----查询条件,不需where

@primaryKey  nvarchar(150),        ----主表的主键 必须为自增量 也就是说 此列不存在任何重复值

@pageCount   int = 1 output,       ----查询结果分页后的总页数

@totalRecord int = 1 output,       ----查询到的记录数

@returnSql   nvarchar(1000) = '' output  -----最后返回的SQL语句

)

AS

SET NOCOUNT ON ---不返回计数(表示受 Transact-SQL 语句影响的行数)

Declare @sqlTmp 	nvarchar(1000)     ----存放动态生成的SQL语句

Declare @strTmp 	nvarchar(1000)     ----存放取得查询结果总数的查询语句

Declare @strID     	nvarchar(1000)     ----存放取得查询开头或结尾ID的查询语句

/*

Declare @sortTypeA 	nvarchar(10)       ----数据排序规则A

Declare @sortTypeB 	nvarchar(10)       ----数据排序规则B

*/

Declare @sqlSelect 	nvarchar(50)	  

Declare @sqlCounts 	nvarchar(50)      

Declare @Erro                 nvarchar(500)    -----错误统计



if  @primaryKey=''

     begin

           set @Erro='没有传入主键'

           return @Erro;

           return

     end



--------生成查询语句--------

--此处@strTmp为取得查询结果数量的语句

set @sqlSelect='select '

set @sqlCounts=' count(1) '

if @filter is null or @filter=''     --没有设置显示条件

	begin

		set @sqlTmp =  @fldName + ' FROM ' + @tblName

		set @strTmp = @sqlSelect+' @totalRecord='+@sqlCounts+' FROM '+@tblName

		set @strID = ' FROM ' + @tblName

	end

else

	begin

		set @sqlTmp =  @fldName + 'FROM ' + @tblName + ' WHERE ' + @filter

		set @strTmp = @sqlSelect+' @totalRecord='+@sqlCounts+' FROM '+@tblName + ' WHERE ' + @filter

		set @strID = ' FROM ' + @tblName + ' WHERE ' + @filter

	end

----取得查询结果总数量-----

exec sp_executesql @strTmp,N'@totalRecord int out ',@totalRecord out

/* 记录总数获取完毕*/



 /*取得分页总数*/

declare @tmpCounts int

if @totalRecord = 0

    set @tmpCounts = 1

else

    set @tmpCounts = @totalRecord

    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize--数据页总数

    

 /*当前页大于总页数 取最后一页*/

    if @page>@pageCount

        set @page=@pageCount

    

/*-----数据分页2分处理-------*/

    declare @pageIndex int --总数/页大小

    declare @lastcount int --总数%页大小 

    set @pageIndex = @tmpCounts/@pageSize

    set @lastcount = @tmpCounts%@pageSize

    if @lastcount > 0

        set @pageIndex = @pageIndex + 1

    else

        set @lastcount = @pagesize     

  /*-----------显示分页-------------*/

    if @filter is null or @filter=''     --没有设置显示条件

    begin

        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理

            begin

                set @strTmp=@sqlSelect+' TOP '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' FROM '+@tblName

                    +' WHERE '+@primaryKey+' NOT IN('+ @sqlSelect+' TOP '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @primaryKey +' FROM '+@tblName

                    +' ORDER BY '+ @sortRule+')'

                    +' ORDER BY '+@sortRule

            end

        else

            begin

            set @page = @pageIndex-@page+1    ------------------------后半部分数据处理

                if @page <= 1 --最后一页数据显示

                   begin

                      set @strTmp='create Table  #TempTable(PagingId int identity(1,1),maidId int) '

                      set @strTmp=@strTmp+'  insert into #TempTable(maidId) '+ @sqlSelect+' TOP '+ CAST(@lastcount as VARCHAR(4))+' '+ @primaryKey+ '  FROM '+@tblName

                        +' ORDER BY '+ @sortRuleReverse

                      set @strTmp=@strTmp+' select '+@fldName+' '+' from '+ @tblName +' ,#TempTable where #TempTable.maidId='+@primaryKey+' and '+@filter+' order by PagingId desc'

                      set @strTmp=@strTmp+' drop table #TempTable'

                   end

                else   

                   begin   

                     set @strTmp='create Table  #TempTable(PagingId int identity(1,1),maidId int) '          

                     set @strTmp=@strTmp+'  insert into #TempTable(maidId) '+@sqlSelect+' TOP '+ CAST(@pageSize as VARCHAR(4))+' '+ @primaryKey+' FROM '+@primaryKey

                        +' WHERE '+@primaryKey+' not in('+ @sqlSelect+' TOP '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @primaryKey +' from '+@tblName

                        +' ORDER BY '+ @sortRuleReverse+')'

                        +' ORDER BY '+ @sortRuleReverse

                    set @strTmp=@strTmp+' select '+@fldName+' '+' from '+ @tblName +' ,#TempTable where #TempTable.maidId='+@primaryKey+' and '+@filter+' order by PagingId desc'

                     set @strTmp=@strTmp+' drop table #TempTable'

                 end

            end

    end

    else --有查询条件

    begin

        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理

        begin

        	    set @strTmp=@sqlSelect+' TOP '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' FROM '+@tblName

        		+' WHERE '+@primaryKey+' NOT IN('+ @sqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @primaryKey +' FROM '+@tblName

        		+' Where  ' + @filter + ' ORDER BY '+@sortRule+') '

        		+' AND ' + @filter + ' ORDER BY '+ @sortRule

        end

        else

        begin 

            set @page = @pageIndex-@page+1 --后半部分数据处理

            if @page <= 1 --最后一页数据显示

                begin

                    set @strTmp='create Table  #TempTable(PagingId int identity(1,1),maidId int) '       

                    set @strTmp=@strTmp+'insert into #TempTable(maidId) '+@sqlSelect+' TOP '+ CAST(@lastcount as VARCHAR(4))+' '+ @primaryKey+' FROM '+@tblName

                        +' WHERE '+ @filter +' ORDER BY '+ @sortRuleReverse

                    set @strTmp=@strTmp+' select '+@fldName+' '+' from '+ @tblName +' ,#TempTable where #TempTable.maidId='+@primaryKey+' and '+@filter+' order by PagingId desc'

                    set @strTmp=@strTmp+' drop table #TempTable'

                end

            else

               begin

                     set @strTmp='create Table  #TempTable(PagingId int identity(1,1),maidId int) '               

                     set @strTmp=@strTmp+'insert into #TempTable(maidId) '+@sqlSelect+' TOP '+ CAST(@pageSize as VARCHAR(4))+' '+ @primaryKey+' FROM '+@tblName

                        +' WHERE '+@primaryKey+' not in('+ @sqlSelect+' TOP '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @primaryKey +' FROM '+@tblName

                        +' WHERE '+ @filter +' ORDER BY '+@sortRuleReverse+') '

                        +' AND '+ @filter +' ORDER BY '+ @sortRuleReverse

                   set @strTmp=@strTmp+' select '+@fldName+' '+' from '+ @tblName +' ,#TempTable where #TempTable.maidId='+@primaryKey+' and '+@filter+' order by PagingId desc'

                    set @strTmp=@strTmp+' drop table #TempTable'

              end

        end    

    end

------返回查询结果-----

set @returnSql = @strTmp

exec sp_executesql @strTmp

SET NOCOUNT OFF

GO
...全文
180 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
fanyuanwaifdl 2009-01-09
  • 打赏
  • 举报
 回复
学习
flairsky 2009-01-08
  • 打赏
  • 举报
 回复
多表联合没做

多表联合做通用的效率可能损失很大
flairsky 2009-01-08
  • 打赏
  • 举报
 回复 
Create procedure [dbo].[uspCustomPaging] 

    @TableName varchar(50),                  --表或视图名

    @Fields varchar(5000) = '*',              --字段名(全部字段为*)

    @OrderFields varchar(5000),            --排序字段(必须!支持多字段,建议建索引)

    @SqlWhere varchar(5000) = '',         --条件语句(如and Name='a')

    @PageSize int,                                     --每页多少条记录

    @PageIndex int = 1 ,                           --指定当前为第几页

    @TotalPages int output                    --返回总页数 

as

begin

    declare @sql nvarchar(4000)

    declare @TotalRecords int   



    --计算总记录数及总页数     

    set @sql = 'select @TotalRecords = count(*) from ' + @TableName + ' where 1=1 ' + @sqlWhere

    exec sp_executesql @sql,N'@totalRecords int output',@TotalRecords output

    select @TotalPages=CEILING((@TotalRecords+0.0)/@PageSize)



    --处理页数超出范围情况

    if @PageIndex<=0 

        set @PageIndex = 1

    if @PageIndex>@TotalPages

        set @PageIndex = @TotalPages



    set @sql = 'select '+ @Fields + ' from (select top(@PageIndex*@PageSize) ' + @Fields + ',row_number() over(order by ' + @OrderFields + ') as rowNumber from ' + @TableName + ' where 1=1 ' + @SqlWhere + ') t where t.rowNumber >= ((@PageIndex-1)*@PageSize+1)'

    

    --print @Sql   

    exec sp_executesql @sql,N'@PageIndex int, @PageSize int',@PageIndex,@PageSize  

end


是不是这意思
德仔 2009-01-08
  • 打赏
  • 举报
 回复
不知楼主有没测试过千万级的数据量的性能
zlhran 2008-12-23
  • 打赏
  • 举报
 回复
哪位有没有比较理想的解决方案呢？ 主要是多字段排序 我也不想用临时表...
等不到来世 2008-12-23
  • 打赏
  • 举报
 回复
有一定可取之处。
海爷的建议很中肯。
另外，在动态语句中操作临时表，
就这种策略来说，性能不高。
zlhran 2008-12-23
  • 打赏
  • 举报
 回复
主要是考虑到性能的问题 如果讲究完全的通用性 那么性能就是问题了
zlhran 2008-12-23
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 xieyueqing 的回复:]
建议不要搞通用的,通用肯定是以牺牲效率为代价的...
[/Quote]
恩各 呵呵 测试时候用的 发布出来会增加的 呵呵
fcuandy 2008-12-23
  • 打赏
  • 举报
 回复
关注
昵称被占用了 2008-12-23
  • 打赏
  • 举报
 回复
不好意思，没有打击搂主的意思
昵称被占用了 2008-12-23
  • 打赏
  • 举报
 回复
其实通用性不够
@primaryKey nvarchar(150), ----主表的主键 必须为自增量 也就是说 此列不存在任何重复值
这里就限制了很多，只能是单字段整型的主键


写得有点太长了，看起来累

看看邹建blog上的分页存储过程可能对你有帮助

昵称被占用了 2008-12-23
  • 打赏
  • 举报
 回复
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句


放sql语句1000很可能不够，放4000或者max（2005或以上版本才支持）
xieyueqing 2008-12-23
  • 打赏
  • 举报
 回复
建议不要搞通用的,通用肯定是以牺牲效率为代价的...
zlhran 2008-12-23
  • 打赏
  • 举报
 回复
自己弱弱的顶下
dobear_0922 2008-12-23
  • 打赏
  • 举报
 回复
支持一下，帮顶。
rockyvan 2008-12-23
  • 打赏
  • 举报
 回复
註釋詳細！
csdyyr 2008-12-23
  • 打赏
  • 举报
 回复
学习
dawugui 2008-12-23
  • 打赏
  • 举报
 回复
看看先.
D3D小游戏，简单实现模型下落
D3D小游戏，简单实现碰撞消失的代码，菜鸟开发专用，能力有限，还望高手雅正 D3D小游戏，简单实现碰撞消失的代码，菜鸟开发专用，能力有限，还望高手雅正 D3D小游戏，简单实现碰撞消失的代码，菜鸟开发专用，能力...
新建 Microsoft Word 文档 (2).docx
关于汇编语言的一点学习和体会，新手刚进坑，如有错漏多还请雅正~
huffman编码 LZW编码 唯一可译码程序代码
信息论huffman编码 LZW编码 唯一可译码程序代码，希望与读者共同学习一下，有不足之处，敬请雅正。
Java面试突击-V2.0
建议阅读本文档的方式 本文档提供详细的目录，建议大家使用电脑阅读。如果大家用手机阅读的话，可以下载一个不错的PDF阅读器，比如 ...笔主能力有限，如果有不对的地方或者和你想法不同的地方，敬请雅正、不舍赐教。
前端正则表达式及书写技巧 收藏就完了
编者不才，如有问题，欢迎雅正，若有收获，请尽情用star羞辱我。另附Github地址 充分利用JavaScript自带原生方法解决问题 前端页面和数据处理虽然看起来简单，但是一个优秀的前端工程师对于细节的把控也是至关重要的...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章