sql - 分页的SQL ROW_NUMBER()问题

我使用ROW_NUMBER()函数从数据库表中获取50×50个项目。
@From参数是从中开始获取50行的行。（第一次是51101151等）
我将参数@CityId传递给存储过程，如果数据库中的前60行cityId=1且cityId = 2位于第61行，则此存储过程不会返回结果。
但如果我从参数51传递@，它就会返回结果。
我做错什么了？

SELECT  RowConstrainedResult.*
FROM    ( SELECT    ROW_NUMBER() OVER
( ORDER BY f.ItemCreatedOnDate DESC ) AS RowNum,
f.*
FROM (
SELECT
      t.ItemIdId,
      t.ItemTypeId,
      t.CreatedOnDate as ItemCreatedOnDate,
      t.CityId as CityId
FROM   dbo.Items    as t
) f) AS RowConstrainedResult
WHERE   RowNum >= @From
    AND RowNum < @From + 50
    AND CityId = @CityId

最佳答案

在您的版本中，ROW_NUMBER()正在枚举所有行。将cityid的谓词移到最里面的select，ROW_NUMBER()将只枚举cityid = 2所在的行。

SELECT RowConstrainedResult.*
FROM (
     SELECT ROW_NUMBER() OVER (ORDER BY f.ItemCreatedOnDate DESC) AS RowNum,
            f.*
     FROM (
          SELECT t.ItemIdId,
                 t.ItemTypeId,
                 t.CreatedOnDate as ItemCreatedOnDate,
                 t.CityId as CityId
          FROM dbo.Items AS t
          WHERE CityId = @CityId
          ) AS f
     ) AS RowConstrainedResult
WHERE RowNum >= @From AND
      RowNum < @From + 50