SQL中TOP函数未按预期返回结果的原因分析

你的问题出在CTE里的row_number() OVER (ORDER BY (SELECT 1))这一行——这个排序方式并没有指定明确的、可重复的排序键，SQL Server会以任意不确定的顺序来分配行号，这直接导致CTE2的结果不可控，最终让TOP 1的结果不符合预期。

一步步拆解逻辑 #

我们先看你的原始数据：

VALUES (1, 'a'), (2, 'b'), (3, 'b'), (4, 'c'), (5, 'c'), (6, 'a'), (7, 'a'), (8, 'b'), (9, 'b')

你原本期望row_number()会按照ordinal从小到大的顺序分配rn，但ORDER BY (SELECT 1)并没有要求数据库这么做。SQL Server在遇到这种没有明确排序键的情况时，会根据存储结构、查询优化器的选择等因素来决定行的处理顺序，这意味着最后两行(8, 'b')和(9, 'b')的顺序可能被颠倒。

当行顺序被颠倒时的情况 #

假设数据库处理最后两行时，先处理(9, 'b')再处理(8, 'b')，那么对应的rn分配会是：

• rn=8 → (9, 'b')
• rn=9 → (8, 'b')

接下来看CTE2的筛选逻辑：

• 对于rn=8的(9, 'b')：它的上一行（rn=7）是(7, 'a')，fruit不同，所以这行会被保留到CTE2中。
• 对于rn=9的(8, 'b')：它的上一行（rn=8）是(9, 'b')，fruit相同，所以这行会被排除。

此时CTE2中会包含(9, 'b')但没有(8, 'b')，当你执行SELECT TOP 1 * FROM cte2 ORDER BY ordinal DESC时，自然会得到fruit:b ordinal:9。

如何修复？ #

要让结果稳定且符合预期，你需要给row_number()指定明确的排序键，确保行号分配的顺序是确定的。把CTE里的排序改成按ordinal排序即可：

;WITH CTE
AS (
 SELECT fruit, ordinal, row_number() OVER (
   ORDER BY ordinal  -- 这里改成明确的排序键
   ) AS rn
 FROM (
  VALUES (1, 'a'), (2, 'b'), (3, 'b'), (4, 'c'), (5, 'c'), (6, 'a'), (7, 'a'), (8, 'b'), (9, 'b')
  ) fruits(ordinal, fruit)
 ), CTE2
AS (
 SELECT fruit, ordinal
 FROM cte AS cteouter
 WHERE rn = 1
  OR fruit != (
   SELECT fruit
   FROM cte AS cteinner
   WHERE cteinner.rn = cteouter.rn - 1
   )
 )
SELECT TOP 1 *
FROM cte2
ORDER BY ordinal DESC

这样row_number()会严格按照ordinal从小到大分配rn，CTE2的结果就会稳定包含(8, 'b')而排除(9, 'b')，最终TOP 1的结果就是你预期的fruit:b ordinal:8。

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