存储过程分页返回查询记录总数有什么好的写法

C5662601 2010-07-20 06:32:43

'select count(*) from (' + @where + ') mt'
这样感觉速度挺慢的

ALTER procedure [dbo].[page_pages]

(@pagesize int,

@pageindex int,

@sort nvarchar(200),

@where nvarchar(max))

as

set nocount on

begin

declare  @strSQL nvarchar(max)

declare @PageLowerBound int

declare @PageUpperBound int

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set @strSQL = '

with aa as(

select top ' + str(@PageUpperBound) + ' orderno = ROW_NUMBER()  OVER (' + @sort + '), * from

(

' + @where + '

)aaa

)

select * from aa where orderno between ' + str(@PageLowerBound + 1) + ' and ' + str(@PageUpperBound + 1)

end

exec(@strSQL)

set @strSQL = 'select count(*) from (' + @where + ') mt'

exec(@strSQL)

set nocount off

...全文

232 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

a157147899 2011-10-28

打赏
举报



 create Proc p_show  @QueryStr nvarchar(4000), --表名、视图名、查询语句，如要加条件，直接在此加入(如：select * from tb where ....)  @PageSize int=10,   --每页的大小(行数)  @pagecount int out, --显示总页数  @PageCurrent int=1,   --要显示的页  @FdShow nvarchar (4000)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段  @FdOrder nvarchar (1000)='' --排序字段列表  as  declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名   ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号   ,@Obj_ID int    --对象ID   ,@sql nvarchar(4000)  --表中有复合主键的处理  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     --显示总页数     set @sql='select @pagecount=ceiling(count(*)*1./@PageSize) from '+@QueryStr  exec sp_executesql @sql,N'@pagecount int out,@PageSize int',@pagecount out,@PageSize    --如果显示第一页，可以直接用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  --调用示例：  declare @pagecount int  exec p_show   	@QueryStr='select name from sysobjects',  	@PageSize=20,  	@pagecount=@pagecount out,  	@PageCurrent=2  select @pagecount AS 页数

永生天地 2010-07-20

打赏
举报

http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html?15304

永生天地 2010-07-20

打赏
举报

[Quote=引用楼主 c5662601 的回复:]
'select count(*) from (' + @where + ') mt'
这样感觉速度挺慢的

SQL code
ALTER procedure [dbo].[page_pages]
(@pagesize int,
@pageindex int,
@sort nvarchar(200),
@where nvarchar(max))
as
set nocount on……
[/Quote]
set @strSQL = 'select count(*) from (' + @where + ') mt'
exec(@strSQL)
没什么可优化的地方了

东那个升 2010-07-20

打赏
举报

[Quote=引用楼主 c5662601 的回复:]
'select count(*) from (' + @where + ') mt'
这样感觉速度挺慢的

SQL code
ALTER procedure [dbo].[page_pages]
(@pagesize int,
@pageindex int,
@sort nvarchar(200),
@where nvarchar(max))
as
set nocount on……
[/Quote]

表上有聚集索引否，，，有的话速度还可以，如果不要太精确可以

--2000

select rows from sysindexes where id = object_id(tablename) and indid in (0,1) 



2005-2008



SELECT  SUM (row_count) AS total_number_of_rows 

FROM sys.dm_db_partition_stats

where object_id=object_id('tablename)    

AND (index_id=0 or index_id=1);

C5662601 2010-07-20

打赏
举报

你这个只返回了每页的数量要总的查询数量[Quote=引用 4 楼 beirut 的回复:]
SQL code
with aa as(
select top ' + str(@PageUpperBound) + ' orderno = ROW_NUMBER() OVER (' + @sort + '), * from
(
' + @where + '
)aaa
)
select * from aa where orderno between ' + str(@PageLow……
[/Quote]

bancxc 2010-07-20

打赏
举报

[Quote=引用 5 楼 beirut 的回复:]
引用 3 楼 c5662601 的回复:
能在我的sql语句基础上优化下吗

合适的加点索引吧
[/Quote]臭流氓

黄_瓜 2010-07-20

打赏
举报

[Quote=引用 3 楼 c5662601 的回复:]
能在我的sql语句基础上优化下吗
[/Quote]
合适的加点索引吧

黄_瓜 2010-07-20

打赏
举报

with aa as(

select top ' + str(@PageUpperBound) + ' orderno = ROW_NUMBER()  OVER (' + @sort + '), * from

(

' + @where + '

)aaa

)

select * from aa where orderno between ' + str(@PageLowerBound + 1) + ' and ' + str(@PageUpperBound + 1)

end

exec(@strSQL)

select @@rowcount()

C5662601 2010-07-20

打赏
举报

能在我的sql语句基础上优化下吗

黄_瓜 2010-07-20

打赏
举报

exec(@strSQL)

select @@rowcount()

htl258_Tony 2010-07-20

打赏
举报

--（原代码作者：邹建,改编加入显示总页数:Tony）

create Proc p_show

@QueryStr nvarchar(4000), --表名、视图名、查询语句，如要加条件，直接在此加入(如：select * from tb where ....)

@PageSize int=10,   --每页的大小(行数)

@pagecount int out, --显示总页数

@PageCurrent int=1,   --要显示的页

@FdShow nvarchar (4000)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段

@FdOrder nvarchar (1000)='' --排序字段列表

as

declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名

 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号

 ,@Obj_ID int    --对象ID

 ,@sql nvarchar(4000)

--表中有复合主键的处理

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

 

--显示总页数

 

set @sql='select @pagecount=ceiling(count(*)*1./@PageSize) from '+@QueryStr

exec sp_executesql @sql,N'@pagecount int out,@PageSize int',@pagecount out,@PageSize



--如果显示第一页，可以直接用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

--调用示例：

declare @pagecount int

exec p_show 

	@QueryStr='select name from sysobjects',

	@PageSize=20,

	@pagecount=@pagecount out,

	@PageCurrent=2

select @pagecount AS 页数  

/*

ID_0E32DC2B-7C79-459A-96B9-9DC1355A97E7 name

--------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

20                                      sysbinobjs

21                                      sysaudacts

22                                      sysobjvalues

23                                      sysclsobjs

24                                      sysrowsetrefs

25                                      sysremsvcbinds

26                                      sysxmitqueue

27                                      sysrts

28                                      sysconvgroup

29                                      sysdesend

30                                      sysdercv

31                                      syssingleobjrefs

32                                      sysmultiobjrefs

33                                      sysguidrefs

34                                      syscompfragments

35                                      sysftstops

36                                      sysqnames

37                                      sysxmlcomponent

38                                      sysxmlfacet

39                                      sysxmlplacement



(20 行受影响)



页数

-----------

14



(1 行受影响)

*/

试试这个。