颠倒分页目前主要流行两种方法：
方法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页成了速度最慢的页面了，然而这些比较慢中间页码又是少有人到达的。
为了更方便使用改进后颠倒分页思路，我已经把它写成了存储过程如下:
ALTER Procedure p_Pagination
(
@TableName nvarchar ( 500 ), --
@ReturnFieldLists nvarchar ( 1800 ), --
@SortFieldLists nvarchar ( 500 ), -- (,)
@PageSize int = 10 , --
@PageIndex int = 1 , --
@WhereClause nvarchar ( 1000 ) = '' -- ( 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 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 +
' 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
/*
调用说明
spc_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',
80,
2,
'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'
*/
70
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
