我这里有一个存储过程,很长很长啊。 --别看他很长,但是效率很高。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则返回 ) as declare @strsql nvarchar(4000) --主语句 declare @strtmp nvarchar(500) --临时变量 declare @strorder nvarchar(500) --总排序方式 declare @stropporder nvarchar(500) --总排序的反方式 declare @strorderfldlist nvarchar(500) --所有要排序的字段序列 declare @selectfld nvarchar(500) --选择主键page declare @selectfirst nvarchar(500) --选择主排序page declare @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=10 if @pageindex<1 set @pageindex=1 if @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 end else 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 end set @stropporder=' order by '+@stropporder set @strorder=' order by '+@strorder +' ' ---------总排序方式end ---------总排序字段序列 set @strorderfldlist='id' if @firstfldname!='' begin set @strorderfldlist=@firstfldname+','+@strorderfldlist if @secondfldname!='' set @strorderfldlist=@secondfldname+','+@strorderfldlist end ---------总排序字段序列end if @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 -------若主排序字段不空,则按主排序字段排序,end else -------若主排序字段为空,则按主键排序, 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 -------若主排序字段为空,则按主键排序,end if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' '+@fields+' from ' + @tblName + '' + @strTmp + ' ' + @strOrder end if @IsCount != 0 begin set @strSQL = 'select count(*) as Total from ' + @tblName + '' if @strWhere!='' set @strSQL='select count(*) as Total from '+@tblName +' where ' +@strWhere end --print @strsql exec (@strSQL) GO
高效分页sql存储过程
最新推荐文章于 2018-05-20 00:04:00 发布