无法使用分页选择每个项目类别中的'n'条记录

我试图在每个ItemCategoryID中选择5条记录进行分页，通过下面的查询，我可以在每个类别中获得5条记录，但分页不起作用，因为我已经声明页面大小为10，但我获得了19条记录，@ItemCounter TotalCount为45…我不知道如何解决它。以下是查询：

DECLARE @PageIndex int = 1
DECLARE @PageSize int = 10
DECLARE @StartRow int
DECLARE @EndRow int
SET @StartRow = (@PageSize * (@PageIndex - 1))  + 1  
SET @EndRow = @PageSize * @PageIndex + 1
DECLARE @ItemCounter int
SELECT @ItemCounter = Count(*)FROM dbo.Auctions WHERE AuctionStatus=1;
WITH Auctions AS
    (
        SELECT  ROW_NUMBER() OVER 
        (PARTITION BY ItemCategoryID  ORDER BY AuctionID) AS RowNumber,                 
            AuctionID, 
            ItemCategoryID  ,                             
            @ItemCounter TotalCount                         
            FROM Auctions                 
            WHERE                 
            AuctionStatus=1 
     )
    SELECT a.* FROM Auctions a  
    WHERE  a.RowNumber <=3 AND a.RowNumber 
    BETWEEN @StartRow AND @EndRow - 1

提前谢谢。

a.RowNumber <=3 AND a.RowNumber BETWEEN @StartRow AND @EndRow - 1

你对此有什么期望？行号必须介于开始和结束之间并且必须小于或等于3？什么是4开始？任何高于3的End都无关紧要。

我将在这里信心倍增，并说对您的问题正确提出的要求是：

对于每个物品类别，最多返回4个状态为1的拍卖
页面显示结果

因此，您显然需要考虑两个计数器，一个用于类别内的拍卖，另一个用于分页：

WITH Auctions AS
(
    SELECT  ROW_NUMBER() OVER 
    (PARTITION BY ItemCategoryID  ORDER BY AuctionID) AS AuctionNumber,                 
        AuctionID, 
        ItemCategoryID                             
        FROM Auctions                 
        WHERE                 
        AuctionStatus=1 
 ),
Paging as (
  SELECT ROW_NUMBER() OVER (ORDER BY ItemCategoryID, AuctionID) as RowNumber
   , a.* 
  FROM Auctions a  
  WHERE AuctionNumber <= 3 
)
SELECT * 
  FROM Paging 
  WHERE  RowNumber BETWEEN @StartRow AND @EndRow - 1;

性能可能会很差，但没有人可以在没有数据模式知识（表定义、索引等）的情况下设计任何性能，而这些知识在问题语句中是缺失的。

TotalRecordCount：

WITH Auctions AS
(
  SELECT  ROW_NUMBER() OVER 
    (PARTITION BY ItemCategoryID  ORDER BY AuctionID) AS AuctionNumber,                 
    AuctionID, 
    ItemCategoryID  ,                             
  FROM Auctions                 
  WHERE AuctionStatus=1 
)
SELECT @total = COUNT(*) 
 FROM Auctions a  
 WHERE AuctionNumber <= 3;

相关内容

最新更新

热门标签：