[已关闭问题] 诚心在园子里求分页存储过程一个
不要not in和不能多字段排序的！！！性能要好至少300W翻页要在1s以下
问题补充：金色海洋（jyk）:
你的哪个倒来倒去的我之前试过了，不行的，如果排序字段有重复时，结果不准确！
老刘：
你的哪个分页我还没试，等回公司我再拿来试试，我不知道你的这个有没有效果，呵呵
Lowkey:RowCount这个只能用在主键排序上，多列排序就不行了！
(4)
|
在网上找了很多，不过很多都不支持多列排序，包括园子里的许多转帖。
我手头有一个，当然也是网上来的，测试能支持多列排序，性能没测试过，因为没有那个数据量，兄台不妨一试：） CREATE PROC SP_PageList @tbname sysname, --要分页显示的表名 @FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC --用于指定排序顺序 @Where nvarchar(1000)='', --查询条件 @RecordCount int OUTPUT, --总记录数 @PageCount int OUTPUT --总页数 AS DECLARE @sql nvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@tbname) IS NULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@tbname) RETURN END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) RETURN END --分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键（或者惟一键）',1,16) RETURN END --其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@Where,N'')=N'' SET @Where=N'' ELSE SET @Where=N'WHERE ('+@Where+N')' --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN SET @sql=N'SELECT @PageCount=COUNT(*)' +N' FROM '+@tbname +N' '+@Where EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT SET @RecordCount = @PageCount SET @PageCount=(@PageCount+@PageSize-1)/@PageSize END --计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+ 其实很简单的 select * from 表名 where 主键字段 in ( select top PageSize 主键字段 from ( select top PageSize * (PageIndex - 1) + 1 主键字段 ,排序字段1,排序字段2,排序字段3 ...... from 表名 order by 排序字段1 desc,排序字段2 desc,排序字段3 desc ......,主键字段 ) as aa order by 排序字段1,排序字段2,排序字段3 ...... ,主键字段 ) order by 排序字段1 desc,排序字段2 desc,排序字段3 desc ......, 主键字段 看着有点乱，不过效率还可以，我试过100万的还可以。 只是有一个缺点：必须有主键，且主键唯一。 另外想要加速的话就要合理设置索引了。 使用了in ，没有使用 not in 。还有就是用了 三次 top ，但是由于中间只提取必要的字段，所以效率会有所提升。 没有看其他人的答案，不知道有没有重复。 CREATE Proc UP_PageShow @QueryStr nvarchar(4000), --表名、视图名、查询语句 @PageSize int=10, --每页的大小(行数) @PageCurrent int=1, --要显示的页 @FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段 @FdOrder nvarchar (1000)='' --排序字段列表 as set nocount on 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( sele 我们这都有用的RowCount分页。性能还挺好的。 |
|
7个月前 老刘. : --第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' '+@Where +N' '+@FieldOrder) ELSE BEGIN SELECT @PageCurrent=@TopN1, @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname +N' '+@Where +N' '+@FieldOrder SET ROWCOUNT @PageCurrent EXEC sp_executesql @sql, N'@n int,@s nvarchar(4000) OUTPUT', @PageCurrent,@sql OUTPUT SET ROWCOUNT 0 IF @sql=N'' EXEC(N'SELECT TOP 0' +N' '+@FieldShow +N' FROM '+@tbname) ELSE BEGIN SET @sql=STUFF(@sql,1,1,N'') --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' WHERE '+@FieldKey +N' IN('+@sql +N') '+@FieldOrder) END END GO |
|
7个月前 金色海洋（jyk） : 你没看到我最后把主键页加导排序字段里了吗？ 如果你的主键没有重复值的话那么就没问题。 如果你的主键是复合主键（或者多表关联后失去了唯一主键）的话，那么就不行了。 另外这个不是一般地颠倒倒来倒去的算法，中间是有点不同的地方的，为了提高效率而作了一些修改。 |
|
|
7个月前 金色海洋（jyk） : 对了要对最后一页的算法作修正。 |
|
|
7个月前 金色海洋（jyk） : 你不觉得分页的前提就是： 最后一个排序字段不能有重复值！ 如果有重复值的话，你用任何方法都是不能准确分页的！两条记录，哪个放在前面？哪个放在后面？排序字段的值都是一样你如何来判断？ 根据存放数据的先后顺序吗？那就是说还是有一个排序“字段”的值是不一样的。 |