存储过程中的分页顺序

Question

我编写了一个从数据库返回业务详细信息的存储过程。页面大小为 10。我也在我的数据库中支付/未支付业务，由布尔列定义（IsPaid真/假）

我想在第一页（或前 10 页）中只显示付费业务。然后在最后一页是未付费的业务。

在我的情况下，使用下面的存储过程。它返回每页的分页顺序。这意味着如果在第一页中没有付费企业，它将返回。

如何将每个页面的顺序更改为所有选择

ALTER PROCEDURE dbo.GetBusUsingPaginNoTown
    @PageNo int,
    @NoOfRecord int,
    @Id int,
    @TotalRecord int output
AS
    select @TotalRecord = count(*) 
    from BusinessFullData
    select * 
    from 
        (select 
             Row_number() over (order by b.Id ASC) as RowNo,
             b.Ispaid, b.Id, b.name, b.Addess, b.DefaultImage,
             t.TownName, c1.CategoryName AS CatN1, 
             c2.CategoryName AS CatN2, c3.CategoryName AS CatN3 
         from 
             BusinessFullData b 
         left join
             Towns t on b.Town = t.Id 
         left join 
             Categories c1 ON b.cat1 = c1.Id 
         left join
             Categories c2 ON b.cat2 = c2.Id 
         left join
             Categories c3 ON b.cat3 = c3.Id 
         where  
              ((b.IsVisable = 1) 
                AND ((b.Cat1 = @Id) OR (b.Cat2 = @Id) OR (b.Cat3 = @Id)
              )) 
        ) as Tab
  where 
      Tab.RowNo between ((@PageNo - 1) * @NoOfRecord) + 1  
                    and (@PageNo * @NoOfRecord) 
  order by 
      IsPaid desc
  return 

Answer 1

over partition子句应包含所需的顺序。在您的情况下

Row_number() over (order by b.Id ASC) as RowNo,

应替换为

Row_number() over (order by b.isPaid ASC) as RowNo,

希望这有帮助。这就是我现在所能看到的，除非你用更清晰的方式描述你的问题......