高效分页sql存储过程
我这里有一个存储过程，很长很长啊。--别看他很长，但是效率很高。5000万条数据亲测。--不知道你用什么变成语言。这里就不不写调用方法了。CREATE proc newgetpage(--默认分页主键为id @tblName nvarchar(128), --表名称列表 @PageSize int=10, --页尺寸 @PageIndex int=1, --当前页 @fields nvarchar(4000)='*', --查询字段列表 @fldname nvarchar(50), --主键字段名 @fldorder bit=1, --主键排序方式，0asc，非零desc @FirstfldName nvarchar(50)='', --主排序字段名，非主键，有重复值 @FirstfldOrder bit=1, --主字段排序方式 @SecondfldName nvarchar(50)='', --副排序字段名，非主键，有重复值 @SecondfldOrder bit=1, --副字段排序方式 @strWhere nvarchar(1000)='', --查询条件 @Iscount bit=0 --返回记录总数，非0则返回)asdeclare @strsql nvarchar(4000) --主语句declare @strtmp nvarchar(500) --临时变量declare @strorder nvarchar(500) --总排序方式declare @stropporder nvarchar(500) --总排序的反方式declare @strorderfldlist nvarchar(500) --所有要排序的字段序列declare @selectfld nvarchar(500) --选择主键pagedeclare @selectfirst nvarchar(500) --选择主排序pagedeclare @selectsecond nvarchar(500) --选择副排序page/**/declare @strwheretmp nvarchar(500) --临时where变量if @strwhere!='' set @strwheretmp=' where 'else set @strwheretmp=''/**/set @strsql=''set @strtmp=''set @strorder=''set @stropporder=''set @strorderfldlist=''set @selectfld=''set @selectfirst=''set @selectsecond=''if @pagesize<1 set @pagesize=10if @pageindex<1 set @pageindex=1if @fields='' set @fields='*'if @FirstfldName='' set @FirstfldName=''if @SecondfldName='' set @SecondfldName=''--------------------------------bengin----------总排序方式if @fldorder!=0 begin -----正排序 set @strorder=@fldname + ' desc ' if @secondfldname!='' if @secondfldorder!=0 set @strorder=@secondfldname + ' desc,'+@strorder else set @strorder=@secondfldname + ' asc,'+@strorder if @firstfldname!='' if @firstfldorder!=0 set @strorder=@firstfldname + ' desc,'+@strorder else set @strorder=@firstfldname + ' asc,'+@strorder -----反排序 set @stropporder=@fldname + ' asc ' if @secondfldname!='' if @secondfldorder!=0 set @stropporder=@secondfldname + ' asc,'+@stropporder else set @stropporder=@secondfldname + ' desc,'+@stropporder if @firstfldname!='' if @firstfldorder!=0 set @stropporder=@firstfldname + ' asc,'+@stropporder else set @stropporder=@firstfldname + ' desc,'+@stropporder endelse begin -----正排序 set @strorder=@fldname + ' asc ' if @secondfldname!='' if @secondfldorder!=0 set @strorder=@secondfldname + ' desc,'+@strorder else set @strorder=@secondfldname + ' asc,'+@strorder if @firstfldname!='' if @firstfldorder!=0 set @strorder=@firstfldname + ' desc,'+@strorder else set @strorder=@firstfldname + ' asc,'+@strorder -----反排序 set @stropporder=@fldname + ' desc ' if @secondfldname!='' if @secondfldorder!=0 set @stropporder=@secondfldname + ' desc,'+@stropporder else set @stropporder=@secondfldname + ' asc,'+@stropporder if @firstfldname!='' if @firstfldorder!=0 set @stropporder=@firstfldname + ' desc,'+@stropporder else set @stropporder=@firstfldname + ' asc,'+@stropporder endset @stropporder=' order by '+@stropporderset @strorder=' order by '+@strorder +' '---------总排序方式end---------总排序字段序列set @strorderfldlist='id'if @firstfldname!='' begin set @strorderfldlist=@firstfldname+','+@strorderfldlist if @secondfldname!='' set @strorderfldlist=@secondfldname+','+@strorderfldlist end---------总排序字段序列endif @Firstfldname!=''-------若主排序字段不空，则按主排序字段排序， begin if @fldorder!=0 set @strTmp = '<=(select top 1' else set @strTmp = '>=(select top 1' set @selectfld=@fldname + replace(@strtmp,'=','')+'('+@fldname+')from(select top ' + str((@PageIndex-1)*@PageSize) + ' '+@strorderfldlist +' from '+@tblname+' '+@strwheretmp+@strwhere +@strorder +')as tbltmp '+ @stropporder +')' if @firstfldname!='' set @selectfirst=' and '+@firstfldname + @strtmp+'('+@firstfldname+')from(select top ' + str((@PageIndex-1)*@PageSize) + ' '+@strorderfldlist +' from '+@tblname+' '+@strwheretmp+@strwhere +@strorder +')as tbltmp '+ @stropporder +')' if @secondfldname!='' set @selectsecond=' and '+@secondfldname + @strtmp+'('+@secondfldname+')from(select top ' + str((@PageIndex-1)*@PageSize) + ' '+@strorderfldlist +' from '+@tblname+' '+@strwheretmp+@strwhere +@strorder +')as tbltmp '+ @stropporder +')' ------------多字段排序代码 set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from ' + @tblName + ' where ' + @selectfld + @selectfirst+ @selectsecond+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from ' + @tblName + ' where ' + @selectfld + @selectfirst+ @selectsecond+' and ' + @strWhere + ' ' + @strOrder ------------多字段排序代码end end-------若主排序字段不空，则按主排序字段排序，endelse-------若主排序字段为空，则按主键排序， begin if charindex('.',@fldname)>0 set @selectfld=substring(@fldname,charindex('.',@fldname)+1,len(@fldname)) else set @selectfld=@fldname if @fldorder!=0 begin set @strTmp = '<(select top 1' set @strOrder = ' order by ' + @fldName +' desc' set @stropporder=' order by '+@selectfld +' asc' end else begin set @strTmp = '>(select top 1' set @strOrder = ' order by ' + @fldName +' asc' set @stropporder=' order by '+@selectfld +' desc ' end set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '(' + @selectfld + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp '+@stropporder +')' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '(' + @selectfld + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp '+@stropporder +') and ' + @strWhere + ' ' + @strOrder end-------若主排序字段为空，则按主键排序，endif @PageIndex = 1begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from ' + @tblName + '' + @strTmp + ' ' + @strOrderendif @IsCount != 0begin set @strSQL = 'select count(*) as Total from ' + @tblName + ''if @strWhere!='' set @strSQL='select count(*) as Total from '+@tblName +' where ' +@strWhereend--print @strsqlexec (@strSQL)GO