我这里有一个存储过程，很长很长啊。--别看他很长，但是效率很高。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 @[email protected] + ' desc ' if @secondfldname!='' if @secondfldorder!=0 set @[email protected] + ' desc,[email protected] else set @[email protected] + ' asc,[email protected] if @firstfldname!='' if @firstfldorder!=0 set @[email protected] + ' desc,[email protected] else set @[email protected] + ' asc,[email protected] -----反排序 set @[email protected] + ' asc ' if @secondfldname!='' if @secondfldorder!=0 set @[email protected] + ' asc,[email protected] else set @[email protected] + ' desc,[email protected] if @firstfldname!='' if @firstfldorder!=0 set @[email protected] + ' asc,[email protected] else set @[email protected] + ' desc,[email protected] endelse begin -----正排序 set @[email protected] + ' asc ' if @secondfldname!='' if @secondfldorder!=0 set @[email protected] + ' desc,[email protected] else set @[email protected] + ' asc,[email protected] if @firstfldname!='' if @firstfldorder!=0 set @[email protected] + ' desc,[email protected] else set @[email protected] + ' asc,[email protected] -----反排序 set @[email protected] + ' desc ' if @secondfldname!='' if @secondfldorder!=0 set @[email protected] + ' desc,[email protected] else set @[email protected] + ' asc,[email protected] if @firstfldname!='' if @firstfldorder!=0 set @[email protected] + ' desc,[email protected] else set @[email protected] + ' asc,[email protected] endset @stropporder=' order by [email protected]set @strorder=' order by [email protected] +' '---------总排序方式end---------总排序字段序列set @strorderfldlist='id'if @firstfldname!='' begin set @[email protected]+',[email protected] if @secondfldname!='' set @[email protected]+',[email protected] end---------总排序字段序列endif @Firstfldname!=''-------若主排序字段不空，则按主排序字段排序， begin if @fldorder!=0 set @strTmp = '<=(select top 1' else set @strTmp = '>=(select top 1' set @[email protected] + replace(@strtmp,'=','')+'([email protected]+')from(select top ' + str((@PageIndex-1)[email protected]) + ' [email protected] +' from [email protected]+' [email protected][email protected] [email protected] +')as tbltmp '+ @stropporder +')' if @firstfldname!='' set @selectfirst=' and [email protected] + @strtmp+'([email protected]+')from(select top ' + str((@PageIndex-1)[email protected]) + ' [email protected] +' from [email protected]+' [email protected][email protected] [email protected] +')as tbltmp '+ @stropporder +')' if @secondfldname!='' set @selectsecond=' and [email protected] + @strtmp+'([email protected]+')from(select top ' + str((@PageIndex-1)[email protected]) + ' [email protected] +' from [email protected]+' [email protected][email protected] [email protected] +')as tbltmp '+ @stropporder +')' ------------多字段排序代码 set @strSQL = 'select top ' + str(@PageSize) + ' [email protected]+' from ' + @tblName + ' where ' + @selectfld + @selectfirst+ @selectsecond+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' [email protected]+' 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 @[email protected] if @fldorder!=0 begin set @strTmp = '<(select top 1' set @strOrder = ' order by ' + @fldName +' desc' set @stropporder=' order by [email protected] +' asc' end else begin set @strTmp = '>(select top 1' set @strOrder = ' order by ' + @fldName +' asc' set @stropporder=' order by [email protected] +' desc ' end set @strSQL = 'select top ' + str(@PageSize) + ' [email protected]+' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '(' + @selectfld + ') from (select top ' + str((@PageIndex-1)[email protected]) + ' ' + @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp [email protected] +')' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' [email protected]+' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '(' + @selectfld + ') from (select top ' + str((@PageIndex-1)[email protected]) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp [email protected] +') and ' + @strWhere + ' ' + @strOrder end-------若主排序字段为空，则按主键排序，endif @PageIndex = 1begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' [email protected]+' from ' + @tblName + '' + @strTmp + ' ' + @strOrderendif @IsCount != 0begin set @strSQL = 'select count(*) as Total from ' + @tblName + ''if @strWhere!='' set @strSQL='select count(*) as Total from [email protected] +' where ' [email protected]end--print @strsqlexec (@strSQL)GO
详细解决方案
相关解决方案