【求】存储过程分页代码【要求：速度快支持百万！可以支持任意字段排序，多表】

Question

本帖最后由 shijianyu00 于 2010-01-28 18:12:28 编辑

Answer 1

http://topic.csdn.net/u/20100128/18/1b7ec38d-7871-4eb5-8c69-e3b598b15c07.html?73767http://topic.csdn.net/u/20100128/18/c05e7b3a-52a6-4671-82cc-fc208715b974.html?19035我提问了2次。如果。有满意的。我给把分全部给你。

Answer 2

sql 2000 or sql 2005

Answer 3

http://topic.csdn.net/u/20080627/12/4a404bb1-6667-466a-925a-4d7bf690695e.html
http://topic.csdn.net/u/20090113/16/cac6480c-84ff-4ae3-8fa1-11cfa39fd8ea.html?28098

Answer 4

目前比较实用的就两三个方案1.用sql 2005的row_number()
2.用top n +max id /min id法
3.所谓的二次top速度差别都不大.
如果要速度快,只能建索引,对大多数条件字段建索引

Answer 5

楼主先直接在sql2005中测试,该存储过程还返回查询用时;
例如:有表名为patent,字段有Patent_id,country_id等,在sql2005中测试语句为:declare @a int
declare @b int
exec Pg_Paging1 'patent','*','patent_id,country_id desc','',10,1,@a output,@b output
select @a,@b@a--返回总记录数
@b--返回查询用时,单位豪秒存储过程代码
CREATE PROCEDURE [dbo].[Pg_Paging1]
(
 @TableName varchar(50),            --表名
 @ReFieldsStr varchar(500) = '*',   --字段名(全部字段为*)
 @OrderString varchar(200),         --排序字段支持多字段不用加order by)((必须要有该项)
 @WhereString varchar(MAX) =N'',    --条件语句(不用加where)
 @PageSize int,                     --每页多少条记录
 @PageIndex int = 1 ,               --指定当前为第几页
 @TotalRecord int output,           --返回总记录数
 @SearchTime decimal(18,4) output   --返回用时
)
AS
BEGIN        --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(MAX);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1;
    set @EndRecord = @StartRecord + @PageSize - 1 ;
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + ' with(nolock) where '+ @WhereString;
            SET @SqlString =@SqlString+ ' with(nolock) where '+ @WhereString;            
        END declare   @d   datetime   
set   @d=getdate() 
    --第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)  set @SearchTime = datediff(ms,@d,getdate())  
 
END

Answer 6

多表的话做视图当表用，不然还真没啥办法
下面这个是2000的CREATE PROC p_page
@Tables   varchar(1000), --表名如testtable
@PrimaryKey   varchar(100),--表的主键,必须唯一性
@Sort   varchar(200)   =   NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)
@CurrentPage   int   =   1,--当前页
@PageSize   int   =   10,---每页大小
@Fields   varchar(1000)   =   '*',--显示的字段列表
@Filter   varchar(1000)   =   NULL,--条件语句,不加where,如 f_id>3
@Group   varchar(1000)   =   NULL,--分组字段
@TotalPage int    output --返回总页数
WITH ENCRYPTION 
AS    SET NOCOUNT ON
    Declare @intResult Int        Begin Tran    DECLARE @sql nvarchar(4000)
    
    if @Filter is null or @Filter=''  
        set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +  @Tables
        else
        set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +  @Tables + ' where ' + @Filter    
 
    EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
    select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数           DECLARE   @strSortColumn   varchar(200)
    DECLARE   @operator   char(2)
    DECLARE   @SortType   char(10)
    DECLARE   @type   varchar(50)
    DECLARE   @prec   int    IF   @Sort   IS   NULL   or   @Sort   =   ''
Begin
    SET   @SortType  = 'ASC'
    SET   @operator   =   '>='
    SET   @Sort   =   ltrim(rtrim(@PrimaryKey))
End    
    Else
Set   @Sort = ltrim(rtrim(@Sort))    SET @strSortColumn = @Sort 

    IF CHARINDEX('DESC',@Sort)>0
      BEGIN
        SET   @strSortColumn   =   SUBSTRING(@Sort, 1, len(@Sort)-4) --REPLACE(@Sort,   'DESC',   '')
        SET   @SortType   =   SUBSTRING(@Sort, len(@Sort)-4,len(@Sort))
        SET   @operator   =   '<='
      END
   
   IF CHARINDEX('ASC',   @Sort)   >   0
      BEGIN  
        SET   @strSortColumn   =   SUBSTRING(@Sort, 1, len(@Sort)-3) --REPLACE(@Sort,   'ASC',   '')
        SET   @SortType   =   SUBSTRING(@Sort, len(@Sort)-3,len(@Sort))
        SET   @operator   =   '>='
      END
    
    Select   @type=t.name,   @prec=c.prec
    FROM   sysobjects   o   
    JOIN   syscolumns   c   on   o.id=c.id
    JOIN   systypes   t   on   c.xusertype=t.xusertype
    Where   o.name   =   @Tables   AND   c.name   =   @strSortColumn
    
    IF   CHARINDEX('char',   @type)   >   0
          SET   @type   =   rtrim(@type)   +   '('   +   CAST(@prec   AS   varchar)   +   ')'
    
    IF CHARINDEX('DESC',@Sort)>0 or CHARINDEX('ASC',   @Sort)  >  0
        SET   @strSortColumn   =   '[' + ltrim(Rtrim(@strSortColumn)) +']'    SET   @Sort = @strSortColumn + ' ' + @SortType    DECLARE   @strPageSize   varchar(50)
    DECLARE   @strStartRow   varchar(50)
    DECLARE   @strFilter   varchar(1000)
    DECLARE   @strSimpleFilter   varchar(1000)
    DECLARE   @strGroup   varchar(1000)
    
    IF @CurrentPage < 1
SET @CurrentPage = 1
    
    SET @strPageSize = CAST(@PageSize   AS   varchar(50))
    SET @strStartRow = CAST(((@CurrentPage   -   1)*@PageSize   +   1)   AS   varchar(50))
    
    IF   @Filter   IS   NOT   NULL   AND   @Filter   !=   ''
      BEGIN
        SET   @strFilter   =   '   Where   '   +   @Filter   +   '   '
        SET   @strSimpleFilter   =   '   AND   '   +   @Filter   +   '   '
      END
    ELSE
      BEGIN
        SET   @strSimpleFilter   =   ''
        SET   @strFilter   =   ''
      END
    
    IF (@Group IS NOT NULL) AND (@Group !=   '')
      SET   @strGroup   =   ' GROUP BY '   +   @Group   +   ' '
    ELSE
      SET   @strGroup   =   ''
    
if @CurrentPage =1
  begin
    set @sql = 'SELECT TOP ' + ltrim(rtrim(str(@PageSize))) + ' ' +   @Fields + ' FROM ' + @Tables + ' ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
  end
else
  begin
    set @sql = 'DECLARE @SortColumn '   +   @type   +   ' SET ROWCOUNT '  +   @strStartRow + ' '  
     + 'Select @SortColumn='   +   @strSortColumn   +   ' FROM '  +   @Tables   
     +  @strFilter   +   ' '   +   @strGroup   +   ' ORDER BY '   +   @Sort   
     + 'SET ROWCOUNT '  +   @strPageSize   +   ' Select '   +   @Fields   
     + ' FROM '   +   @Tables   +   ' Where '   +   @strSortColumn   
     + @operator   +   ' @SortColumn '   +   @strSimpleFilter   +   ' '   
     + @strGroup   +   ' ORDER BY '   +   @Sort
  end
  
    exec(@sql)    If @@Error <> 0
       Begin
           RollBack Tran
           Return -1
       End
    Else
       Begin
           Commit Tran
           Return @intResult ---返回记录总数
       End
GO

Answer 7

/*--用存储过程实现的分页程序
 显示指定表、视图、查询结果的第X页
 对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
 如果视图或查询结果中有主键,不推荐此方法--邹建 2003.09(引用请保留此信息)--*//*--调用示例
 exec p_show '地区资料' exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GOCREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10,   --每页的大小(行数)
@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
 ,@Obj_ID int    --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
 ,@strjoin nvarchar(4000) --连接字段
 ,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页，可以直接用top来完成
if @PageCurrent=1 
begin
 select @Id1=cast(@PageSize as varchar(20))
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
 return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
 select @Id1=cast(@PageSize as varchar(20))
  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
 begin
  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
   goto lbusetemp  --如果表中无主键,则用临时表处理  select @FdName=name from syscolumns where id=@Obj_ID and colid in(
   select colid from sysindexkeys where @Obj_ID=id and indid in(
    select indid from sysindexes where @Obj_ID=id and name in(
     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
   )))
  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin
   select @strfd='',@strjoin='',@strwhere=''
   select @strfd=@strfd+',['+name+']'
    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
    ,@strwhere=@strwhere+' and b.['+name+'] is null'
    from syscolumns where id=@Obj_ID and colid in(
    select colid from sysindexkeys where @Obj_ID=id and indid in(
     select indid from sysindexes where @Obj_ID=id and name in(
      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
    )))
   select @strfd=substring(@strfd,2,2000)
    ,@strjoin=substring(@strjoin,5,4000)
    ,@strwhere=substring(@strwhere,5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity: 
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr
  +' where '+@FdName+' not in(select top '
  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
  +')'+@FdOrder
  )
 return/*--表中有复合主键的处理方法--*/
lbusepk:  
 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
  (select top 100 percent * from '+@QueryStr+@FdOrder+') a
  left join (select top '+@Id2+' '+@strfd+' 
  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
  where '+@strwhere+') a'
  )
 return/*--用临时表处理的方法--*/
lbusetemp:  
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
  into #tb from'+@QueryStr+@FdOrder+'
 select '+@FdShow+' from #tb where '+@FdName+' between '
 +@Id1+' and '+@Id2
 )
GO本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/18/4566261.aspx

Answer 8

我是看的200分来的！！！Create Procedure dbo.p_PageList
@tb varchar(150), --表名
@col varchar(50), --按该列来进行分页(必须是唯一性的字符，比如标识种子)
@colorder varchar(50), --需要排序的字段(为空，则默认为col)
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@countwhat varchar(500),--要统计的字段列表
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(1000),--查询条件
@pages int OUTPUT, --总页数
@countresult varchar(500) OUTPUT --统计结果
AS
SET NOCOUNT ON
DECLARE @coltemp varchar(50)
Declare @intResult Int
DECLARE @spos INT,@epos INT,@n INT
DECLARE @temp nvarchar(400)
DECLARE @r1 decimal(15,4),@r2 decimal(15,4),@r3 decimal(15,4),@r4 decimal(15,4),@r5 decimal(15,4),@r6 decimal(15,4),@r7 decimal(15,4),@r8 decimal(15,4),@r9 decimal(15,4),@r10 decimal(15,4)
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @colorder is null or rtrim(@colorder)='' 
set @colorder = @col
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件 
SET @where1=' Where ' 
SET @where2=' '
END
ELSE
BEGIN--有查询条件 
SET @where1=' Where ('+@condition+') AND '--本来有条件再加上此条件 
SET @where2=' Where ('+@condition+') '--原本没有条件而加上此条件
END
SET @temp=''
SET @n=1
IF LEN(@countwhat)>0
BEGIN
IF RIGHT(@countwhat,1)<>',' SET @countwhat=@countwhat + ','
SET @spos=1
SET @epos=CHARINDEX(',', @countwhat, @spos)
WHILE @epos>@spos
BEGIN
IF LEFT(SUBSTRING(@countwhat,@spos,@epos-@spos),3)='sum'
SET @temp=@temp + ',@r' + CAST(@n AS nvarchar(5)) + '=isnull(' + SUBSTRING(@countwhat,@spos,@epos-@spos) + ',0.0)'
ELSE
SET @temp=@temp + ',@r' + CAST(@n AS nvarchar(5)) + '=' + SUBSTRING(@countwhat,@spos,@epos-@spos)
SET @spos=@epos+1
SET @epos=CHARINDEX(',', @countwhat, @spos)
SET @n=@n+1
END
END
WHILE @n<=10
BEGIN
SET @temp=@temp + ',@r' + CAST(@n AS nvarchar(5)) + '=0'
SET @n=@n+1
ENDSET @sql='Select @r=COUNT(*)' + @temp + ' FROM '+@tb+@where2
print @sql
EXEC sp_executesql @sql,N'@r int OUTPUT,@r1 decimal(15,4) OUTPUT,@r2 decimal(15,4) OUTPUT,@r3 decimal(15,4) OUTPUT,@r4 decimal(15,4) OUTPUT,@r5 decimal(15,4) OUTPUT,@r6 decimal(15,4) OUTPUT,@r7 decimal(15,4) OUTPUT,@r8 decimal(15,4) OUTPUT,@r9 decimal(15,4) OUTPUT,@r10 decimal(15,4) OUTPUT',@intResult OUTPUT,@r1 OUTPUT,@r2 OUTPUT,@r3 OUTPUT,@r4 OUTPUT,@r5 OUTPUT,@r6 OUTPUT,@r7 OUTPUT,@r8 OUTPUT,@r9 OUTPUT,@r10 OUTPUT --计算总记录数
Select @pages=CEILING((@intResult+0.0)/@pagesize)--计算总页数
SET @countresult=CAST(@r1 AS varchar) + ',' + CAST(@r2 AS varchar) + ',' + CAST(@r3 AS varchar) + ',' + CAST(@r4 AS varchar) + ',' + CAST(@r5 AS varchar) + ',' + CAST(@r6 AS varchar) + ',' + CAST(@r7 AS varchar) + ',' + CAST(@r8 AS varchar) + ',' + CAST(@r9 AS varchar) + ',' + CAST(@r10 AS varchar)
SET @spos=CHARINDEX('.', @col)
IF @spos>0 SET @coltemp='t.'+RIGHT(@col,LEN(@col)-@spos)
ELSE SET @coltemp=@col
IF @orderby=0 SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+@col+'>(Select MAX('+@coltemp+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+' FROM '+@tb+@where2+'ORDER BY '+@colorder+') t) orDER BY '+@colorder
ELSE SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+@where1+@col+'<(Select MIN('+@coltemp+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+' FROM '+@tb+@where2+'ORDER BY '+@colorder+' DESC) t) orDER BY '+@colorder+' DESC'
IF @page=1 SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+@where2+'ORDER BY '+@colorder+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
print @sql
EXEC(@sql)If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @intResult
End
SET NOCOUNT OFF

Answer 9

 下面按任意字段排序的分页存储过程http://blog.csdn.net/taito/archive/2005/12/22/558990.aspx
我在原来的加上了返回记录总数
CREATE PROCEDURE dbo.GetPagingRecord
    (
        @tablename varchar(100),--表名或视图表
        @fieldlist varchar(4000)='*',--欲选择字段列表
        @orderfield varchar(100),--排序字段
        @keyfield varchar(100),--主键
        @pageindex int,--页号,从0开始
        @pagesize int=20,--页尺寸      
        @strwhere varchar(4000),--条件
        @ordertype bit=1--排序,1,降序,0,升序
    )
AS
/**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
    SET NOCOUNT ON
    declare @sqlstr varchar(6000)
    --处理SQL中危险字符,并且将条件处理成易嵌入的形式
    set @strwhere=replace(@strwhere,'''','''''')
    set @strwhere=replace(@strwhere,'--','')
    set @strwhere=replace(@strwhere,';','')
    set @sqlstr='declare @CurPageNum int;'
    set @sqlstr=@sqlstr+'declare @nextpagenum int;'
    set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
    set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
    set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
    if @ordertype=1
    begin
    set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from 
 
'+@tablename+'   where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in ( 
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';'
    end
    else
    begin
    set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from 
 
'+@tablename+'   where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in ( 
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';'
    end
      
    begin
    set @sqlstr =@sqlstr + ' select count(*) as Total from ' + @tablename + ' where  '+ @strwhere + ';'
    end    set @sqlstr=@sqlstr+'execute( @sqlstr)'
    --print @sqlstr
    execute(@sqlstr)
GO-----------------------------------------------------------------------exec GetPagingRecord 'product','id,c_code,p_code,price','price','id',1,10,'1=1',1