TSQL CTE（公共表达式）的错误

Question

我正在使用Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)标准版（64位）在Windows NT 6.0上（Build 6002：Service Pack 2）

描述：

1. [ORC_RESULT]和GROUPING_SELECT是CTE表。

我遇到了关于CTE（公共表达式）的奇怪错误。

SELECT a.keysbor
--,gs.[rank]
FROM [ORC_RESULT] AS a inner JOIN GROUPING_SELECT AS gs
    ON a.keysbor = gs.keysbor
WHERE gs.[RANK] = 1
ORDER BY a.keysbor

结果 36 行（无重复行）

SELECT a.keysbor
,gs.[rank]
FROM [ORC_RESULT] AS a inner JOIN GROUPING_SELECT AS gs
    ON a.keysbor = gs.keysbor
WHERE gs.[RANK] = 1 
ORDER BY a.keysbor

结果29行

Q：为什么只有在SELECT语句中存在 gs.[rank] 字段时，筛选器 gs.[RANK] = 1 才会应用？

without gs.[rank]   with gs.[rank]  rank
10072002992443  10072002992443       1
10072002992444  10072002992444       1
10072002992445  10072002992445       1
10072002992446  10072002992446       1
10072002992447  10072002992447       1
10072002992448  10072002992448       1
10072002992449  10072002992449       1
10072002992450  10072002992450       1
10072002992451  10072002992451       1
10072002992452  10072002992452       1
10072002992453  10072002992453       1
10072002992454  10072002992454       1
10072002992455  10072002992455       1
10072002992456  10072002992456       1
10072002992457  10072002992457       1
10072002992458  10072002992458       1
10072002992459  10072002992459       1
10072002992460  10072002992460       1
10072002992461                       2
10072002992462                       2
10072002992463                       2
10072002992464                       2
10072002992465                       2
10072002992466                       2
10072002992467                       2
10072002992736  10072002992736       1
10072002992866  10072002992866       1
10072002992867  10072002992867       1
10072002992868  10072002992868       1
10072002992869  10072002992869       1
10072002992870  10072002992870       1
10072002992871  10072002992871       1
10072002992872  10072002992872       1
10072002992873  10072002992873       1
10072002992874  10072002992874       1
10072002992875  10072002992875       1

- AllmanTool

---

是的，我在 GROUPING_SELECT CTE 表中使用 ROW_NUMBER() OVER(PARTITION BY...ORDER BY )。 - AllmanTool

使用ROW_NUMBER()生成的列使用名称“RANK”有点混淆，因为还有另一个可以在同一位置使用的函数称为“RANK()”。无法完全分析您的整个查询，但这将归结为您的ORDER BY子句（在窗口函数中）不够具体，以便唯一地定义其分区内行的顺序。因此，对查询进行轻微更改将生成不同的计划，并将指定不同的行为“1”。 - Damien_The_Unbeliever

谢谢您的评论。这个查询有特定的业务逻辑。有一件事我不明白，为什么选择语句会影响到where语句。 - AllmanTool

请注意，您的SQL Server已经超过5年了（并且不再受支持）。虽然查询中的错误比引擎中的错误更有可能，但您可能希望尝试在更新的版本上进行复制（安装SQL Server 2016的免费版，还原数据库）。即使限制在SQL Server 2008上，最后一个SP也是SP4，并且已经修复了ROW_NUMBER()错误（尽管这些错误涉及并行性，而此查询不使用）。 - Jeroen Mostert

这是我以前的一个答案，它演示了一个玩具示例，说明如何使用未约束的ORDER BY可能会影响返回的行数。 - Damien_The_Unbeliever

Answer 1

我认为你的[rank]计算方式不够确定，即

PARTITION BY A.[KEYKRT], A.[VIDSBR],A.[ORC_ID_ED] ORDER BY A.[ORC_ID_ED] 

row_number()函数不能唯一确定顺序，因此根据计划运算符的不同，row_number()可能会将1分配给不同的行。

我举一个例子，其中计划相同，但处理的行的顺序不同，使用相同的输入数据将得到2个不同的结果。根据您在SELECT中放置的列，优化器构建不同的计划，并且您的RANK等于不同的行（对应于不同的keysbor），因此当涉及keysbor连接时，返回不同数量的行。

因此，在这两种情况下，都应用了过滤器rank = 1，但是1被分配给不同的行。

以下是示例：

    declare @t table( a int, b int, c int, primary key(c desc, b)); /*run it as it is, then uncomment PK and run again*/
    insert into @t values 
    (1,1,1), (1,1,2), (1,1,3),
    (1,2,1), (1,2,2), (1,2,3);

    declare @t1 table( c int);
    insert @t1 values (1);

    with cte as (
    select row_number() over (partition by a, b order by b) as rn,
           a,
           b,
           c
    from @t 
    group by a, b, c
    )

    select *
    from cte c join @t1 t on c.c = t.c
    where rn = 1;

如您所见，我总是使用相同的输入，在第一种情况下我没有声明主键，在第二种情况下我声明了主键。返回的行不同，并不意味着过滤器不起作用，只是意味着在第一种情况下，rn = 1被归属于c = 1的2行，但在第二种情况下，rn = 1有两行，分别是c=1和c=3。

- sepupic