mssql sql高效分页方法代码

mssql sql高效分页方法代码

alter procedure dbo.proc_listpage
(
@tblname nvarchar(200), ----要显示的表或多个表的连接
@fldname nvarchar(500) = '*', ----要显示的字段列表
@pagesize int = 1, ----每页显示的记录个数
@page int = 10, ----要显示那一页的记录
@pagecount int = 1 output, ----查询结果分页后的总页数
@counts int = 1 output, ----查询到的记录数
@fldsort nvarchar(200) = null, ----排序字段列表或条件
@sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列sort指代最后

一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' sorta

asc,sortb desc,sortc ')
@strcondition nvarchar(1000) = null, ----查询条件,不需where
@id nvarchar(150), ----主表的主键
@dist bit = 0 ----是否添加查询字段的 distinct 默认0不添加/1添加
)
as
set nocount on
declare @sqltmp nvarchar(1000) ----存放动态生成的sql语句
declare @strtmp nvarchar(1000) ----存放取得查询结果总数的查询语句
declare @strid nvarchar(1000) ----存放取得查询开头或结尾id的查询语句

declare @strsorttype nvarchar(10) ----数据排序规则a
declare @strfsorttype nvarchar(10) ----数据排序规则b

declare @sqlselect nvarchar(50) ----对含有distinct的查询进行sql构造
declare @sqlcounts nvarchar(50) ----对含有distinct的总数查询进行sql构造

if @dist = 0
begin
  set @sqlselect = 'select '
  set @sqlcounts = 'count(*)'
end
else
begin
  set @sqlselect = 'select distinct '
  set @sqlcounts = 'count(distinct '+@id+')'
end

if @sort=0
begin
  set @strfsorttype=' asc '
  set @strsorttype=' desc '
end
else
begin
  set @strfsorttype=' desc '
  set @strsorttype=' asc '
end

--------生成查询语句--------
--此处@strtmp为取得查询结果数量的语句
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
  set @sqltmp = @fldname + ' from ' + @tblname
  set @strtmp = @sqlselect+' @counts='+@sqlcounts+' from '+@tblname
  set @strid = ' from ' + @tblname
end
else
begin
  set @sqltmp = + @fldname + 'from ' + @tblname + ' where (1>0) ' +

@strcondition
  set @strtmp = @sqlselect+' @counts='+@sqlcounts+' from '+@tblname + '

where (1>0) ' + @strcondition
  set @strid = ' from ' + @tblname + ' where (1>0) ' + @strcondition
end

----取得查询结果总数量-----
exec sp_executesql @strtmp,n'@counts int out ',@counts out
declare @tmpcounts int
if @counts = 0
  set @tmpcounts = 1
else
  set @tmpcounts = @counts

  --取得分页总数
  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 @strcondition is null or @strcondition='' --没有设置显示条件
  begin
  if @pageindex<2 or @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据

处理
  begin 
  set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+

@fldname+' from '+@tblname
  +' where '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-1)

as varchar(20)) +' '+ @id +' from '+@tblname
  +' order by '+ @fldsort +' '+ @strfsorttype+')'
  +' order by '+ @fldsort +' '+ @strfsorttype 
  end
  else
  begin
  set @page = @pageindex-@page+1 --后半部分数据处理
  if @page <= 1 --最后一页数据显示
  set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as

varchar(4))+' '+ @fldname+' from '+@tblname
  +' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+

@fldsort +' '+ @strfsorttype 
  else  
  set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as

varchar(4))+' '+ @fldname+' from '+@tblname
  +' where '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)

+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
  +' order by '+ @fldsort +' '+ @strsorttype+')'

  +' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+

@fldsort +' '+ @strfsorttype 
  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 '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-1)

as varchar(20)) +' '+ @id +' from '+@tblname
  +' where (1>0) ' + @strcondition + ' order by '+ @fldsort +' '+

@strfsorttype+')'
  +' ' + @strcondition + ' order by '+ @fldsort +' '+ @strfsorttype  
  end
  else
  begin 
  set @page = @pageindex-@page+1 --后半部分数据处理
  if @page <= 1 --最后一页数据显示
  set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as

varchar(4))+' '+ @fldname+' from '+@tblname
  +' where (1>0) '+ @strcondition +' order by '+ @fldsort +' '+

@strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
  else
  set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as

varchar(4))+' '+ @fldname+' from '+@tblname
  +' where '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)

+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
  +' where (1>0) '+ @strcondition +' order by '+ @fldsort +' '+

@strsorttype+')'
  + @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as

temptb'+' order by '+ @fldsort +' '+ @strfsorttype 
  end  
  end

------返回查询结果-----
exec sp_executesql @strtmp
--print @strtmp
set nocount off