分页的存储过程如下，有一麻烦。该如何解决

分页的存储过程如下，有一麻烦。
分页的存储过程如下：

create procedure GetAllShopListWithPic
(@PageNumber int,
@ShopsPerPage int,
@HowManyShops int output)
as

declare @Shop table
(RowNumber int,
SID int,
Name nvarchar(50),
IsProm bit)

insert into @Shop

select Row_number() over (order by shop.SID),SID,Name,IsProm
from Shop

select @HowManyShops=count(SID) from @Shop

select SID,Name,IsProm
from @Shop
where
RowNumber>(@PageNumber-1)*@ShopsPerPage
and RowNumber<=@PageNumber*@ShopsPerPage
order by SID DESC

如果按SID的正序来排列的话这种分页很好。
但是一旦我设置order by SID DESC 。采取倒序的排列就有问题。

比如我设置了每页显示3个信息。它的排列则是：
第一页是3，2，1
第二页是6，5，4

我想要的是它这样排列：6，5，4，3，2，1（从第一页到第二页）

有没有好的解决方法呢？？？

[解决办法]
很久没来了，没想到还可以来接分啊，哈哈哈，友情顶一下
[解决办法]
jf
[解决办法]
接分.
[解决办法]
送分就接
[解决办法]

探讨
顺便问一下。

学习ajax的话应该如何下手，很模糊的问题，没人回答就算了哈！继续三分。

[解决办法]
发一个row_number()函数分页的小例子.

没事了可以看一下.

SQL code

ALTER PROCEDURE [dbo].[usp_User_SearchByUserName]    @PageSize int,    @CurrentPage int,    @UserName varchar(50),    @SortBy varchar ( 50 ),    @IsAscOrder bit = 1,    @ItemCount int OUTPUTASBEGIN    SET NOCOUNT ON        DECLARE @UpperBand int, @LowerBand int    -- Calculate the @LowerCount and @UpperCount    SET @LowerBand  = (@CurrentPage - 1) * @PageSize    SET @UpperBand  = (@CurrentPage * @PageSize) + 1    SET @ItemCount = (SELECT COUNT(*) FROM [User] WHERE FirstName LIKE '%' + @UserName + '%');    WITH tempPagedUser AS    (        SELECT    UserID,                FirstName,                LastName,                Age,                Memo,                ROW_NUMBER() OVER (ORDER BY                                        CASE WHEN @SortBy='UserID' AND @IsAscOrder = 1 Then UserID END,                                        CASE WHEN @SortBy='UserID' AND @IsAscOrder = 0 Then UserID END DESC,                                        CASE WHEN @SortBy='FirstName' AND @IsAscOrder = 1 Then FirstName END,                                        CASE WHEN @SortBy='FirstName' AND @IsAscOrder = 0 Then FirstName END DESC,                                        CASE WHEN @SortBy='LastName' AND @IsAscOrder = 1 Then LastName END,                                        CASE WHEN @SortBy='LastName' AND @IsAscOrder = 0 Then LastName END DESC,                                        CASE WHEN @SortBy='Age' AND @IsAscOrder = 1 Then Age END,                                        CASE WHEN @SortBy='Age' AND @IsAscOrder = 0 Then Age END DESC,                                        UserID DESC                                                                            ) AS RowNumber         FROM    [User]         WHERE    FirstName LIKE '%' + @UserName + '%'    )    SELECT    [UserID] [User_UserID],            [FirstName] [User_FirstName],            [LastName] [User_LastName],            [Age] [User_Age],            [Memo] [User_Memo]    FROM    tempPagedUser     WHERE    RowNumber &gt; @LowerBand AND RowNumber &lt; @UpperBand        -- Get The Count Of The Rows That They Meet the Criteria    RETURN @ItemCount    SET NOCOUNT OFF    END
------解决方案--------------------  
接分.
[解决办法]
友情帮顶。
[解决办法]
也许我还能蹭点 :)
[解决办法]
接分。
[解决办法]
...........
[解决办法]
jf
[解决办法]
我的分的存程，希望主有用

SQL codeUSE [IQSupport]GO/****** Object:  StoredProcedure [dbo].[Pro_PaginationBase]    Script Date: 04/09/2008 10:29:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Pro_PaginationBase](    @TableName            VARCHAR(20)                -- 表名    ,@PageSize            INT                        -- 尺寸    ,@PageNumber        INT                        --     ,@ColumnsName        VARCHAR(100)            -- 需要返回的列     ,@Sort                VARCHAR(20)             -- 排序字段名    ,@WhereClause        VARCHAR(100)            -- 件子句    ,@PageTotalCount      INT    OUTPUT         -- )ASBEGIN    SET NOCOUNT ON;-- 如果字段空，那么索全部字段IF @ColumnsName = ''BEGIN    SET @ColumnsName = '*'END-- 如果排序字段空，那么以id排序IF @Sort = ''BEGIN    SET @Sort = 'id'END---- 如果件子句空，那么就等于 1 = 1 ，因where后必要子句IF @WhereClause = ''BEGIN    SET @WhereClause = ' 1 = 1 ' END----------------------------------- 行算 --------------------------------------------DECLARE @RowStart   int  -- 始行DECLARE @RowEnd     int  -- 束行SET @PageNumber = @PageNumber -1                -- 是-1的。SET @RowStart = @PageSize * @PageNumber + 1;    -- 起始行 = 每 &#215; 前 + 1SET @RowEnd = @RowStart + @PageSize - 1 ;       -- 束行 = 起始 + 每 - 1------------------------------------------------------EXEC('WITH TEMP AS (     SELECT ' + @ColumnsName + ', ROW_NUMBER() OVER (ORDER BY ' + @Sort + ') AS RowNumber     FROM ' + @TableName + ' WHERE ' + @WhereClause + ') select * from TEMP     Where RowNumber &gt;= ' + @RowStart + ' and RowNumber &lt;= ' + @RowEnd ) ------------------------------------- 取共的 ------------------------------------------DECLARE @RowTotalCount INTDECLARE @SQLString NVARCHAR(500)SET @SQLString = 'SELECT @RowTotalCount = COUNT(*) FROM ' + @TableName + ' WHERE ' + @WhereClauseEXEC SP_EXECUTESQL @SQLString,N'@RowTotalCount INT OUTPUT',@RowTotalCount OUTPUTIF @RowTotalCount%@PageSize = 0BEGIN -- 如果行/尺寸能整除，那么就直接等于    SET @PageTotalCount = @RowTotalCount/@PageSizeENDELSEBEGIN -- 如果行/尺寸能整除，那么就+1    SET @PageTotalCount = @RowTotalCount/@PageSize + 1END----------------------------------------------------------END
[解决办法]
了一字，

BEGIN -- 如果行/尺寸不能整除，那么就+1
   SET @PageTotalCount = @RowTotalCount/@PageSize + 1
END

[解决办法]
谢谢

分页的存储过程如下有一麻烦。该如何

热点推荐