分布存储过程
颠倒分页目前主要流行两种方法：
方法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页成了速度最慢的页面了，然而这些比较慢中间页码又是少有人到达的。
为了更方便使用改进后颠倒分页思路，我已经把它写成了存储过程如下:
代码
CREATE PROCEDURE p_Pagination
(
@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'*/
(
@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'*/
