ROW_NUMBER() OVER PARTITION偶现跳过行号1问题及解决方案咨询

嘿，我来帮你理清这个问题的根源，然后给你几个可行的解决方案：

问题根源分析 #

你的核心问题出在窗口函数计算和过滤操作的顺序颠倒了。原查询里，你先给所有RollAction记录生成了分区行号，再通过关联RollPermission、Roll以及WHERE条件过滤行。这就导致某些分区中row=1、row=2的行因为不满足关联条件被过滤掉，剩下的行虽然属于该分区，但行号大于1，最终被WHERE row=1直接排除，看起来就像“跳过了这个分区”。

至于你提到“移除所有WHERE和内联仍有行号跳过”，这大概率是个误解——ROW_NUMBER()函数在分区内是连续且唯一的，每个分区的行号必然从1开始递增，绝对不会跳过。你可以单独执行下面的查询验证：

SELECT RollId, TimeStamp,
       ROW_NUMBER() OVER (PARTITION BY RollId ORDER BY TimeStamp DESC) AS row
FROM RollAction
WHERE RollType = 'Test' AND Deleted = 0 AND UserId = 5
ORDER BY RollId, row;

你会看到每个RollId分区的行号都是从1开始连续的，不会出现跳号情况。

解决方案 #

方案1：先过滤关联，再生成行号（推荐） #

把关联RollPermission、Roll的逻辑移到窗口函数计算之前，这样生成的行号是基于已经符合关联条件的记录，每个分区的row=1就是该分区中最新且满足要求的行，不会再出现“跳过分区”的问题。

修改后的SQL：

SELECT a.*, rp.TestId, r.Deleted
FROM (
    -- 先筛选符合关联条件的RollAction，再生成行号
    SELECT ra.*,
           ROW_NUMBER() OVER (PARTITION BY ra.RollId ORDER BY ra.TimeStamp DESC) AS row
    FROM RollAction ra
    INNER JOIN RollPermission rp 
        ON rp.RoId = ra.RollId 
        AND rp.RoType = ra.RoType 
        AND rp.UserId = ra.UserId 
        AND rp.Deleted = 0
    INNER JOIN Roll r 
        ON r.Id = ra.RollId 
        AND r.RoType = ra.RollType 
        AND r.Deleted = 0
    WHERE ra.Action = 'Fetched' 
      AND ra.RollType = 'Test' 
      AND ra.Deleted = 0 
      AND ra.UserId = 5
) a
WHERE a.row = 1
ORDER BY a.TimeStamp DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

方案2：取每个分区过滤后剩余行的最小行号 #

如果你坚持要先给所有RollAction生成行号再过滤，那可以用MIN(row) OVER (PARTITION BY RollId)标记每个分区剩余行的最小行号，再筛选出这些行：

SELECT filtered.*, rp.TestId, r.Deleted
FROM (
    SELECT a.*,
           -- 标记每个分区剩余行的最小行号
           MIN(a.row) OVER (PARTITION BY a.RollId) AS min_row_in_partition
    FROM (
        SELECT *, 
               ROW_NUMBER() OVER (PARTITION BY RollId ORDER BY TimeStamp DESC) AS row
        FROM RollAction
        WHERE Action = 'Fetched' 
          AND RollType = 'Test' 
          AND Deleted = 0 
          AND UserId = 5
    ) a
    INNER JOIN RollPermission rp 
        ON rp.RoId = a.RollId 
        AND rp.RoType = a.RoType 
        AND rp.UserId = a.UserId 
        AND rp.Deleted = 0
    INNER JOIN Roll r 
        ON r.Id = a.RollId 
        AND r.RoType = a.RollType 
        AND r.Deleted = 0
) filtered
WHERE filtered.row = filtered.min_row_in_partition
ORDER BY filtered.TimeStamp DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

这个方案会保留每个分区中剩余行里最新的那一条，不管它的原始行号是多少。

方案3：用CTE优化可读性 #

和方案1逻辑一致，但用CTE（公共表表达式）写法更清晰，适合复杂查询的维护：

WITH FilteredRollActions AS (
    SELECT ra.*, rp.TestId, r.Deleted
    FROM RollAction ra
    INNER JOIN RollPermission rp 
        ON rp.RoId = ra.RollId 
        AND rp.RoType = ra.RoType 
        AND rp.UserId = ra.UserId 
        AND rp.Deleted = 0
    INNER JOIN Roll r 
        ON r.Id = ra.RollId 
        AND r.RoType = ra.RollType 
        AND r.Deleted = 0
    WHERE ra.Action = 'Fetched' 
      AND ra.RollType = 'Test' 
      AND ra.Deleted = 0 
      AND ra.UserId = 5
),
RankedActions AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY RollId ORDER BY TimeStamp DESC) AS row
    FROM FilteredRollActions
)
SELECT *
FROM RankedActions
WHERE row = 1
ORDER BY TimeStamp DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

总结 #

方案1和方案3是最优选择，它们先过滤无效记录再生成行号，不仅逻辑清晰，性能也更好。方案2适合特殊场景，但因为要先计算所有行的行号，性能会略差一些。

内容的提问来源于stack exchange，提问作者LittleMygler