颠倒分页目前主要流行两种方法:
方法A:
select * from Products where productid in ( select top 10 productid from ( select top 100 productid from Products order by productid ) as t order by t.productid desc )order by productid
优点:很好的利用了主键,是IT界公认的最佳分页方式之一。
缺点:分页的数据数据表必须具备主键。这也使得这种思路在通用性上稍差了一点。
方法B:
select * from ( select top 10 * from ( select top 100 * from Products order by productid ) as t order by t.productid desc) as t order by productid
优点:通用性比较好,几乎可以对任何数据进行分页。
缺点:如果表中记录条数在1W以上,且表字段比较多。读取后面几页数据时,几乎要把表中的数据读取出来完。这样的性能和速度是可想而知的。
有时候在做程序开发时,我们都想保持高性能又不失通用性。这里提供一种中间办法,可能代大家参考。
如果某一类信息有1000页以上,在大多用户有这样一个浏览习惯,前10页和最后10页是占总浏览量的95%以上(如果在翻页条上,没有直达中间页码的快捷功能,会更高)。也就是说,随着某一类信息的页码不断增多,浏览的页就主要集中在了前10页和最后10页。
假设某一类信息共分1000页,每页10条数据,共1W条数据。根据上述特点,我们做一个前500页和后500页的二计算。
前500页:仍然按照方法B计算。
最后500页:把数据从后往前读取。
例如:
读取第1000页的数据,只我们只读取最后10条数据,再做一次颠倒排序便可。
select * from ( select top 10 * from Products order by productid desc) as t order by t.productid asc
读取第999页的数据,只我们只读取最后20条数据,再做一次颠倒排序,读取前10条数据便可。
select top 10 * from ( select top 20 * from Products order by productid desc) as t order by t.productid asc
以次类推.......
颠倒这样改进后,翻第499页和第500页成了速度最慢的页面了,然而这些比较慢中间页码又是少有人到达的。
为了更方便使用改进后颠倒分页思路,我已经把它写成了存储过程如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
(
@TableName NVARCHAR( 500 ), --
@ReturnFieldLists NVARCHAR( 2000 ), --
@SortFieldLists NVARCHAR( 500 ), -- (,)
@PageSize INT = 10 , --
@PageIndex INT = 1 , --
@WhereClause NVARCHAR( 800 ) = '' -- ( Where )
)AS
BEGIN
-- 全局变量
DECLARE @strSQL NVARCHAR( 4000 ), --
@strSQLcount NVARCHAR( 2000 ),
@InnerOrder NVARCHAR( 500 ),
@TotalPage INT,
@TotalRecord INT,
@YuShu INT,
@i INT
SET @WhereClause = RTRIM(LTRIM(@WhereClause));
SET @WhereClause = ISNULL(@WhereClause, '' );
SET @InnerOrder = @SortFieldLists
-- 颠倒排序字段计算变量
DECLARE @Index INT,
@DotIndex INT,
@SortFieldTemp NVARCHAR( 500 ),
@strTemp NVARCHAR( 500 ),
@strOrder1 NVARCHAR( 500 ),
@strOrder2 NVARCHAR( 500 )
------------------------------------------------------------------------------------
-- 计算页码和总记录条数开始
BEGIN
IF @WhereClause <> ''
SET @strSQLcount = ' Select @c=COUNT(1) From ' + @TableName + ' Where ' + @WhereClause + ' ; ' ;
ELSE
SET @strSQLcount = ' Select @c=COUNT(1) From ' + @TableName + ' ; ' ;
EXECUTE sp_ExecuteSQL @stmt = @strSQLcount,@params = N ' @c as int OUTPUT ' ,@c = @TotalRecord OUTPUT;
SET @YuShu = @TotalRecord % @PageSize;
SET @TotalPage = (CASE WHEN @YuShu > 0 THEN @TotalRecord / @PageSize + 1 ELSE @TotalRecord / @PageSize END)
END
SELECT @TotalPage TotalPage,@TotalRecord TotalRecord
-- 计算页码和总记录条数结束 --------------------------------------------------------------------------------------
-- 读取本页数据开始
BEGIN
IF (@PageIndex > @TotalPage) OR (@TotalRecord = 0 ) -- 超出最大页码或总记录条数为0
BEGIN
--------------------------------------------------------------------------------------
SET @strSQL = ' Select TOP 1 ' + @ReturnFieldLists + ' From ' + @TableName + ' where 1<>1 '
--------------------------------------------------------------------------------------
END
ELSE IF (@PageIndex = 1 ) -- 第1页
BEGIN
--------------------------------------------------------------------------------------
IF @WhereClause <> ''
SET @strSQL = ' Select TOP ' + STR(@PageSize) +
' ' + @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @InnerOrder;
ELSE
SET @strSQL = ' Select TOP ' + STR(@PageSize) +
' ' + @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @InnerOrder;
--------------------------------------------------------------------------------------
END
ELSE IF (@PageIndex = @TotalPage) -- 最后一页
BEGIN
--------------------------------------------------------------------------------------
-- 计算颠倒排序字段
SET @strOrder1 = ''
SET @strOrder2 = ''
SET @i = 0
WHILE( 1 = 1 )
BEGIN
-- 防止死循环
SET @i = @i + 1 ;
IF(@i > 50 )
BREAK;
SELECT @Index = CHARINDEX( ' , ' , @SortFieldLists )
IF @Index = 0
BEGIN
SET @SortFieldTemp = @SortFieldLists;
SELECT @DotIndex = CHARINDEX( ' . ' , @SortFieldTemp )
IF @DotIndex > 0
SET @strTemp = SUBSTRING(@SortFieldTemp, @DotIndex + 1 , LEN(@SortFieldTemp) - @DotIndex + 1 )
ELSE
SET @strTemp = @SortFieldTemp
END
ELSE
BEGIN
SET @SortFieldTemp = SUBSTRING(@SortFieldLists, 1 , @Index - 1 )
SELECT @DotIndex = CHARINDEX( ' . ' , @SortFieldTemp )
IF @DotIndex > 0
SET @strTemp = SUBSTRING(@SortFieldTemp, @DotIndex + 1 , LEN(@SortFieldTemp) - @DotIndex + 1 )
ELSE
SET @strTemp = @SortFieldTemp
END
-- 组织顺排序字段
SET @strOrder1 = @strOrder1 + ' , ' + @strTemp
-- 组织反排序字段
SELECT @DotIndex = CHARINDEX( ' desc ' , @SortFieldTemp)
IF @DotIndex > 0
SET @strOrder2 = @strOrder2 + ' , ' + REPLACE(@SortFieldTemp, ' desc ' , ' asc ' )
ELSE
BEGIN
SELECT @DotIndex = CHARINDEX( ' asc ' ,@SortFieldTemp)
IF @DotIndex > 0
SET @strOrder2 = @strOrder2 + ' , ' + REPLACE(@SortFieldTemp, ' asc ' , ' desc ' )
ELSE
SET @strOrder2 = @strOrder2 + ' , ' + @SortFieldTemp + ' desc '
END
-- 取得下一循环的数据
IF @Index = 0
BREAK;
ELSE
SELECT @SortFieldLists = SUBSTRING(@SortFieldLists, @Index + 1 , LEN(@SortFieldLists) - @Index + 1 )
END
--
SELECT @strOrder1 = SUBSTRING(@strOrder1, 2 , LEN(@strOrder1))
SELECT @strOrder2 = SUBSTRING(@strOrder2, 2 , LEN(@strOrder2))
--------------------------------------------------------------------------------------
-- 第后一页的SQL
IF @WhereClause <> ''
SET @strSQL = ' select * from ( ' +
' Select TOP ' + STR(CASE WHEN (@YuShu > 0 ) THEN @YuShu ELSE @PageSize END) +
' ' + @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @strOrder2 +
' ) t order by ' + @strOrder1;
ELSE
SET @strSQL = ' select * from ( ' +
' Select TOP ' + STR(CASE WHEN (@YuShu > 0 ) THEN @YuShu ELSE @PageSize END) +
' ' + @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @strOrder2 +
' ) t order by ' + @strOrder1;
--------------------------------------------------------------------------------------
END
ELSE IF (@PageIndex > @TotalPage / 2 ) -- 二分后,后一半页面
BEGIN
--------------------------------------------------------------------------------------
-- 计算颠倒排序字段
SET @strOrder1 = ''
SET @strOrder2 = ''
SET @i = 0
WHILE( 1 = 1 )
BEGIN
-- 防止死循环
SET @i = @i + 1 ;
IF(@i > 50 )
BREAK;
SELECT @Index = CHARINDEX( ' , ' , @SortFieldLists )
IF @Index = 0
BEGIN
SET @SortFieldTemp = @SortFieldLists;
SELECT @DotIndex = CHARINDEX( ' . ' , @SortFieldTemp )
IF @DotIndex > 0
SET @strTemp = SUBSTRING(@SortFieldTemp, @DotIndex + 1 , LEN(@SortFieldTemp) - @DotIndex + 1 )
ELSE
SET @strTemp = @SortFieldTemp
END
ELSE
BEGIN
SET @SortFieldTemp = SUBSTRING(@SortFieldLists, 1 , @Index - 1 )
SELECT @DotIndex = CHARINDEX( ' . ' , @SortFieldTemp )
IF @DotIndex > 0
SET @strTemp = SUBSTRING(@SortFieldTemp, @DotIndex + 1 , LEN(@SortFieldTemp) - @DotIndex + 1 )
ELSE
SET @strTemp = @SortFieldTemp
END
-- 组织顺排序字段
SET @strOrder1 = @strOrder1 + ' , ' + @strTemp
-- 组织反排序字段
SELECT @DotIndex = CHARINDEX( ' desc ' , @SortFieldTemp)
IF @DotIndex > 0
SET @strOrder2 = @strOrder2 + ' , ' + REPLACE(@SortFieldTemp, ' desc ' , ' asc ' )
ELSE
BEGIN
SELECT @DotIndex = CHARINDEX( ' asc ' ,@SortFieldTemp)
IF @DotIndex > 0
SET @strOrder2 = @strOrder2 + ' , ' + REPLACE(@SortFieldTemp, ' asc ' , ' desc ' )
ELSE
SET @strOrder2 = @strOrder2 + ' , ' + @SortFieldTemp + ' desc '
END
-- 取得下一循环的数据
IF @Index = 0
BREAK;
ELSE
SELECT @SortFieldLists = SUBSTRING(@SortFieldLists, @Index + 1 , LEN(@SortFieldLists) - @Index + 1 )
END
--
SELECT @strOrder1 = SUBSTRING(@strOrder1, 2 , LEN(@strOrder1))
SELECT @strOrder2 = SUBSTRING(@strOrder2, 2 , LEN(@strOrder2))
--------------------------------------------------------------------------------------
-- 二分后,后一半页面
IF @WhereClause <> ''
SET @strSQL = ' select top ' + STR(@PageSize) +
' * from ( ' +
' Select TOP ' + STR(CASE WHEN (@YuShu > 0 ) THEN ((@TotalPage - @PageIndex) * @PageSize + @YuShu) ELSE (@TotalPage - @PageIndex + 1 ) * @PageSize END) +
' ' + @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @strOrder2 +
' ) t order by ' + @strOrder1;
ELSE
SET @strSQL = ' select * from ( ' +
' Select TOP ' + STR(CASE WHEN (@YuShu > 0 ) THEN ((@TotalPage - @PageIndex) * @PageSize + @YuShu) ELSE (@TotalPage - @PageIndex + 1 ) * @PageSize END) +
' ' + @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @strOrder2 +
' ) t order by ' + @strOrder1;
--------------------------------------------------------------------------------------
END
ELSE --- 二分后,前一半页面
BEGIN
--------------------------------------------------------------------------------------
-- 计算颠倒排序字段
SET @strOrder1 = ''
SET @strOrder2 = ''
SET @i = 0
WHILE( 1 = 1 )
BEGIN
-- 防止死循环
SET @i = @i + 1 ;
IF(@i > 50 )
BREAK;
SELECT @Index = CHARINDEX( ' , ' , @SortFieldLists )
IF @Index = 0
BEGIN
SET @SortFieldTemp = @SortFieldLists;
SELECT @DotIndex = CHARINDEX( ' . ' , @SortFieldTemp )
IF @DotIndex > 0
SET @strTemp = SUBSTRING(@SortFieldTemp, @DotIndex + 1 , LEN(@SortFieldTemp) - @DotIndex + 1 )
ELSE
SET @strTemp = @SortFieldTemp
END
ELSE
BEGIN
SET @SortFieldTemp = SUBSTRING(@SortFieldLists, 1 , @Index - 1 )
SELECT @DotIndex = CHARINDEX( ' . ' , @SortFieldTemp )
IF @DotIndex > 0
SET @strTemp = SUBSTRING(@SortFieldTemp, @DotIndex + 1 , LEN(@SortFieldTemp) - @DotIndex + 1 )
ELSE
SET @strTemp = @SortFieldTemp
END
-- 组织顺排序字段
SET @strOrder1 = @strOrder1 + ' , ' + @strTemp
-- 组织反排序字段
SELECT @DotIndex = CHARINDEX( ' desc ' , @strTemp)
IF @DotIndex > 0
SET @strOrder2 = @strOrder2 + ' , ' + REPLACE(@strTemp, ' desc ' , ' asc ' )
ELSE
BEGIN
SELECT @DotIndex = CHARINDEX( ' asc ' , @strTemp)
IF @DotIndex > 0
SET @strOrder2 = @strOrder2 + ' , ' + REPLACE(@strTemp, ' asc ' , ' desc ' )
ELSE
SET @strOrder2 = @strOrder2 + ' , ' + @strTemp + ' desc '
END
-- 取得下一循环的数据
IF @Index = 0
BREAK;
ELSE
SELECT @SortFieldLists = SUBSTRING(@SortFieldLists, @Index + 1 , LEN(@SortFieldLists) - @Index + 1 )
END
--
SELECT @strOrder1 = SUBSTRING( @strOrder1, 2 , LEN(@strOrder1))
SELECT @strOrder2 = SUBSTRING( @strOrder2, 2 , LEN(@strOrder2))
--------------------------------------------------------------------------------------
-- 二分后,前一半页面
IF @WhereClause <> ''
SET @strSQL = ' select * from (select top ' + STR(@PageSize) +
' * from ( ' + ' Select TOP ' + STR(@PageIndex * @PageSize) + ' ' + @ReturnFieldLists +
' From ' + @TableName +
' Where ' + @WhereClause +
' Order By ' + @InnerOrder +
' ) t order by ' + @strOrder2 +
' ) t order by ' + @strOrder1;
ELSE
SET @strSQL = ' select * from (select top ' + STR(@PageSize) +
' * from ( ' + ' Select TOP ' + STR(@PageIndex * @PageSize) + ' ' + @ReturnFieldLists +
' From ' + @TableName +
' Order By ' + @InnerOrder +
' ) t order by ' + @strOrder2 +
' ) t order by ' + @strOrder1;
--------------------------------------------------------------------------------------
END
END
-- print @strSQL;
-- print ' xx ' ;
-- print @strSQLcount;
-- SQL
EXECUTE sp_ExecuteSQL @strSQL;
-- 读取本页数据结束
END -----------------------------------------------------------------------------------
------------------------------------------------
-- 调用示例
/* p_Pagination 'NEWGXE_Order a, NEWGXE_Transaction b, NEWGXE_ProductTypeImage c',
'b.PaypalAccount_nvarchar,count(a.OrderID_int) as OrderCount,sum(a.USDmoney_money) as USDmoney_money',
'USDmoney_money DESC', 10, 1,
'a.OrderID_int=b.OrderID_int and a.ProductType_char=c.ProductType_char and a.Approve_char=''Yes'' and b.PaymentStatus_char=''Completed'' and a.ShipStatus_char=''Fini'' and DateDiff(d,''2007-09-01'', b.PaymentTime_nvarchar) >= 0 and DateDiff(d, b.PaymentTime_nvarchar, ''2007-10-01'') >=0 group by b.PaypalAccount_nvarchar having sum(a.USDmoney_money) > 0' */